超过一定限制时，没有MySQL记录返回吗? [英] No MySQL records return when past a certain limit?

问题描述

我有以下查询，返回250条记录:

I have the following query which returns 250 records:

SELECT DISTINCT p.* FROM Persons AS p
                      INNER JOIN Colors AS c ON c.ColorId = p.FavoriteColorId 
WHERE p.Name = 'John Doe' AND c.ColorName IN ('RED','BLUE','YELLOW')
                      LIMIT 240,10;

-- Returns 198 records
SELECT DISTINCT p.* FROM Persons AS p
                      INNER JOIN Colors AS c ON c.ColorId = p.FavoriteColorId 
WHERE p.Name = 'John Doe' AND c.ColorName IN ('RED','BLUE','YELLOW')

-- Returns 250 records. Why?
SELECT DISTINCT COUNT(*) FROM Persons AS p
                      INNER JOIN Colors AS c ON c.ColorId = p.FavoriteColorId 
WHERE p.Name = 'John Doe' AND c.ColorName IN ('RED','BLUE','YELLOW')

执行上述操作时，即使共有250条记录，我也看不到任何记录.当我将LIMIT更改为LIMIT 197,10时，它才开始返回记录，然后我看到其中的1条记录.

When I execute the above, I see no records even though there are 250 total. It only starts to return records when I change the LIMIT to LIMIT 197,10, then I see 1 of the records.

为什么会这样?

推荐答案

在错误的位置上您与众不同.计算所有行(250)并选择不同的行值.只有1行，即250.

You have distinct in the wrong place for the count. Your counting all rows (250) and selecting the distinct row values. There is only 1 row which is 250.

您的意思是select count(distinct p.*)，然后将返回198

What you meant was select count(distinct p.*) Which will then return 198

这篇关于超过一定限制时，没有MySQL记录返回吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！