mysql - mysql 查询中计数不同的原因是什么？

Question

当我使用 SQL_CALC_FOUND_ROWS 运行以下查询或使用 IN 在子查询中指定所有颜色 RED、BLUE、YELLOW 时子句中，我得到的记录数为 198。但是，当我单独处理每种颜色时，我得到的 COUNT 总计为 248 (72 + 59 + 118)。额外的 50 行从哪里来？我本以为如果我单独执行它们然后将它们相加，我会得到 198，但我得到 248。我是否看到 IN 子句的工作方式不正确？我更愿意使用 COUNT 而不是 SQL_CALC_FOUND_ROWS，因为我发现 SQL_CALC_FOUND_ROWS 速度要慢得多(+3 秒)

以下是一些统计数据:

1. I am running MySQL Version: 5.5.2 
2. The tables engine is INNODB.
3. All the `CHECK TABLE .... EXTENDED` return OK.

-- Returns 198
SELECT SQL_CALC_FOUND_ROWS DISTINCT p.* FROM Persons AS p
INNER JOIN Colors AS c ON c.PersonId = p.Id
WHERE p.Name = 'John Doe' AND c.ColorName IN ('YELLOW','RED','BLUE');
SELECT FOUND_ROWS();

-- Returns 198
SELECT COUNT(1) as MyCount FROM (SELECT DISTINCT p.* FROM Persons AS p
INNER JOIN Colors AS c ON c.PersonId = p.Id
WHERE p.Name = 'John Doe' AND c.ColorName IN ('YELLOW','RED','BLUE')) all;

-- Returns 72
SELECT COUNT(1) as MyCount FROM (SELECT DISTINCT p.* FROM Persons AS p
INNER JOIN Colors AS c ON c.PersonId = p.Id
WHERE p.Name = 'John Doe' AND c.ColorName IN ('RED')) red;

-- Returns 59
SELECT COUNT(1) as MyCount FROM (SELECT DISTINCT p.* FROM Persons AS p
INNER JOIN Colors AS c ON c.PersonId = p.Id
WHERE p.Name = 'John Doe' AND c.ColorName IN ('BLUE')) blue;

-- Returns 118
SELECT COUNT(1) as MyCount FROM (SELECT DISTINCT p.* FROM Persons AS p
INNER JOIN Colors AS c ON c.PersonId = p.Id
WHERE p.Name = 'John Doe' AND c.ColorName IN ('YELLOW')) yellow;

Answer 1

类似的事情正在发生:

人员表

ID  Name
--  ------------
 1  John Doe
 2  Xaisoft

颜色表

PersonID  ColorName
--------  ---------
       1  Red
       1  Yellow
       2  Yellow

现在在加入 Colors 时查询 Persons.* 中的 John Doe:

 SELECT p.*
 FROM Persons p
 INNER JOIN Colors C ON c.PersonID = p.ID
 WHERE p.Name = 'John Doe';

对于 John Doe 下的每种颜色，您的结果将是一个 p.* 行:

ID  Name
--  ------------
 1  John Doe
 1  John Doe

如果您对此应用 COUNT DISTINCT，您最终会得到 1。

如果您分别计算Red 和Yellow，则每个查询都会得到一个。将它们相加，您将得到 2。

<小时/>

这样看:如果您扔掉 COUNT 并仅 SELECT DISTINCT Persons.*，您会得到以下结果:

约翰·多伊的红色或黄色:

 SELECT DISTINCT p.*
 FROM Persons p
 INNER JOIN Colors C ON c.PersonID = p.ID
 WHERE p.Name = 'John Doe'
   AND c.ColorName IN ('RED', 'YELLOW');

ID  Name
--  ----------
 1  John Doe

红色仅适用于 John Doe:

 SELECT DISTINCT p.*
 FROM Persons p
 INNER JOIN Colors C ON c.PersonID = p.ID
 WHERE p.Name = 'John Doe'
   AND c.ColorName IN ('RED');

ID  Name
--  ----------
 1  John Doe

黄色仅适用于 John Doe:

 SELECT DISTINCT p.*
 FROM Persons p
 INNER JOIN Colors C ON c.PersonID = p.ID
 WHERE p.Name = 'John Doe'
   AND c.ColorName IN ('YELLOW');

ID  Name
--  ----------
 1  John Doe