MySQL选择带有子查询和LIMIT [英] MySQL select with sub-query and LIMIT

问题描述

我正在使用subselect来获取我需要的行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行，有时我选择不符合

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

其中c.active = 1

WHERE c.active = 1

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

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

有什么主意我该如何解决?即使子选择不符合WHERE子句，我如何获得子选择的最后一个ID?

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(外部联接)的右侧表上使用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

这篇关于MySQL选择带有子查询和LIMIT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！