©2020-2024 ioDraw All rights reserved,
Privacy Policy
如果将 count(*) 和 group by 一起使用，count(*) 统计的将会是每个 group 里面的行数，而不是 group 的个数。
如果你想统计 group 的个数，需要将 group by 查询放到子查询里面，然后在主查询里面再使用 count(*)。
如下所示，第一个查询的 count(*) 统计的只是特定的 Category 和 Year 下的总行数，而第二个查询才是第一个查询的 group 的个数。
mysql> SELECT tag AS Category, YEAR(created) AS Year,
COUNT(*) AS Counts FROM fyi_links GROUP BY tag,
YEAR(created);
+----------+------+--------+
| Category | Year | Counts |
+----------+------+--------+
| DBA | 2005 | 1 |
| DBA | 2006 | 2 |
| DEV | 2004 | 1 |
| DEV | 2006 | 1 |
| SQA | 2003 | 1 |
| SQA | 2006 | 1 |
+----------+------+--------+
6 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM (
SELECT tag AS Category, YEAR(created) AS Year,
COUNT(*) AS Counts FROM fyi_links GROUP BY tag,
YEAR(created) ) groups;
+----------+
| COUNT(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)