MySQL计数返回0而不是没有记录

Question

我在 Stackoverflow 上搜索了这个答案，但未能找到任何适合我的已发布解决方案。

我有两个表:

builds:
build_id (INT)
status_id (INT)
created (INT)

还有

statuses:
status_id (INT)
name (VARCHAR)
description (VARCHAR)

我想要一个查询，该查询将返回日期、构建的状态以及该日期和状态的计数。如果特定状态的计数为 0，我希望它返回 0。例如:

+------------+-------+----------+
| date       | count | name     |
+------------+-------+----------+
| 2013-12-05 |     1 | Failed   |
| 2013-12-05 |     2 | Stable   |
| 2013-12-05 |     1 | Unstable |
| 2013-12-06 |     0 | Failed   |
| 2013-12-06 |     1 | Stable   |
| 2013-12-06 |     1 | Unstable |
+------------+-------+----------+

我在 sql 查询中尝试了各种左连接和 ifnull 组合。以下是返回不包含 0 计数的结果的查询:

SELECT FROM_UNIXTIME(created, '%Y-%m-%d') as date,
COUNT(statuses.status_id) as count, statuses.name
FROM builds
LEFT JOIN statuses on builds.status_id=statuses.status_id
GROUP BY date, name;

此查询显示:

+------------+-------+----------+
| date       | count | name     |
+------------+-------+----------+
| 2013-12-05 |     1 | Failed   |
| 2013-12-05 |     2 | Stable   |
| 2013-12-05 |     1 | Unstable |
| 2013-12-06 |     2 | Stable   |
| 2013-12-06 |     1 | Unstable |
+------------+-------+----------+

提前谢谢您。

就其值(value)而言，此处发布的解决方案有效:MySQL Group by - Get columns with zero count

除了他们要求在 where 子句中包含公司名称(在我的情况下为日期字段)...我想在一个查询中获取所有日期的列表。

Answer 1

尝试:

SELECT FROM_UNIXTIME(created, '%Y-%m-%d') as date,
       COUNT(builds.status_id) as count, 
       statuses.name
FROM builds
RIGHT JOIN statuses on builds.status_id=statuses.status_id
GROUP BY date, name;

或者也许:

SELECT FROM_UNIXTIME(created, '%Y-%m-%d') as date,
       SUM(CASE WHEN builds.status_id IS NULL THEN 0 ELSE 1 END ) as count, 
       statuses.name
FROM builds
RIGHT JOIN statuses on builds.status_id=statuses.status_id
GROUP BY date, name;

COUNT(column) 理论上应该只计算空值，但谁知道......