MySQL 选择子查询和限制

2022-01-23 00:00:00 limit select subquery mysql

我正在使用子选择来获取我需要的行 ID，如下所示:

I am using the subselect to get the row IDs I need like this: 

SELECT
   p.id, c.id as category_id 
FROM
   (SELECT id FROM products p WHERE p.id > 6319055 ORDER BY id LIMIT 1000) prods 
LEFT JOIN 
   products p ON p.id = prods.id 
LEFT JOIN 
   categories c ON (c.id = p.category_id) 
WHERE
   c.active = 1

ID 6319055 是我最后选择的 ID.我选择数据后保存.

The ID 6319055 is my last selected ID. I save it after selecting the data.

现在我遇到的问题是我在每个周期中选择 1000 行，并且在某些时候我选择了 1000 行不符合

Now the problem I am having is that I am selecting 1000 rows on each cycle and at some point I select 1000 rows which doesn't meet the

WHERE c.active = 1

WHERE c.active = 1

要求.选择不返回任何内容，并且我没有任何行 ID 来继续子选择.

requirements. Select returns nothing and I don't have any row ID to continue the subselect.

有什么想法可以解决这个问题吗?如何获取子选择的最后一个 ID，即使它不符合 WHERE 子句?

Any ideas how could I solve this? How can I get the last ID of the sub select, even if it doesn't meet the WHERE clause?

推荐答案

当你在 LEFT JOIN (Outer Join) 的右侧表中使用 WHERE 条件时，它实际上变成了一个 INNER JOIN，因为 WHERE 子句需要匹配条件.这就是为什么您只会遇到 c.active = 1 的情况.

When you use WHERE condition on the right-side table of a LEFT JOIN (Outer Join), it effectively becomes an INNER JOIN, because WHERE clause needs to match the conditions. That is why you are only getting cases where c.active = 1.

您需要将 WHERE 条件转换为 LEFT JOIN .. ON .. AND .. 条件:

You need to shift the WHERE condition to LEFT JOIN .. ON .. AND .. condition:

SELECT
   p.id, c.id as category_id 
FROM
   (SELECT id FROM products p WHERE p.id > 6319055 ORDER BY id LIMIT 1000) prods 
LEFT JOIN 
   products p ON p.id = prods.id 
LEFT JOIN 
   categories c ON c.id = p.category_id 
                   AND c.active = 1

相关文章