COUNT(*)返回错误的数字

问题描述：

看来我什么都不懂.请考虑此查询

It seems I don't get it something. Please consider this query

SELECT COUNT(*) AS `numrows` 
FROM (`exp_channel_titles` ch) 
JOIN `exp_channel_data` cd ON `cd`.`entry_id`=`ch`.`entry_id` 
LEFT JOIN `exp_matrix_data` md ON `md`.`entry_id`=`ch`.`entry_id` and field_id = 14 
LEFT JOIN `exp_assessment_users` au ON `au`.`entry_id`=`ch`.`entry_id` 
WHERE ch.channel_id = 4 GROUP BY `ch`.`entry_id`

它返回2

但是如果我将其更改为

SELECT * 
FROM (`exp_channel_titles` ch) 
JOIN `exp_channel_data` cd ON `cd`.`entry_id`=`ch`.`entry_id` 
LEFT JOIN `exp_matrix_data` md ON `md`.`entry_id`=`ch`.`entry_id` and field_id = 14 
LEFT JOIN `exp_assessment_users` au ON `au`.`entry_id`=`ch`.`entry_id` 
WHERE ch.channel_id = 4 GROUP BY `ch`.`entry_id`

结果仅是1行.怎么这样?

result is 1 row only. How so?

答

您正在分组，这意味着内部匹配的行被折叠为单个实体.例如考虑这样的假表:

You're grouping, which means internally matching rows are collapsed into a single entity. e.g. consider a fake table like this:

field
-----
a
a

是的，一个字段表包含两个记录，两个记录中的值都为a.

Yes, a one field table, with two records, both of which have the value a in them.

SELECT *
FROM table
GROUP BY field

group by将查找所有具有相同值的字段，并将它们折叠为SINGLE记录，因此您的两个a记录在结果集中成为一行，最后得到

group by will find all fields which have the same value, and collapse them down into a SINGLE record, so your two records of a become one row in the result set, and you end up with

field
-----
a

但是做

SELECT count(*)
FROM table
GROUP BY field

改变事物.现在，数据库将逐字地计算将多少记录折叠到结果集的单行中.因此，您仍然在结果集中获得一个单行，其中包含group by折叠的行数:

changes things. Now the DB will literally count how many records were collapsed down into the single row of result set. So you still get a SINGLE row in the result set, which contains a count of how many rows were collapsed by the group by:

count(*)
--------
2

值为2的一行，因为有两行带有a.

One row, with a value of 2, because there were two rows with a.

现在，如果您有一个包含更多记录的表:

Now if you had a table with more records:

field
-----
a
a
b
c
c
c

您会得到:

SELECT * ... GROUP BY field

field
-----
a
b
c

SELECT count(*), field ... GROUP BY field

count(*)   field
----------------
2          a
1          b
3          c

还是3行结果，但请注意计数是如何表示原始表中每个分组字段的数量.

again, 3 rows of results, but note how the count represents how many of each grouped field there are in the original table.

相关推荐