MySQL查询指定日期区间注册数：无数据时返回0而非空结果

嘿，这个问题我太熟悉了！你当前的SQL只会返回有用户注册的日期，因为它是从Users表里直接取存在的记录分组——哪天没人注册，那条记录根本就不会出现在结果集里。要搞定无数据日期显示0的需求，核心思路是先生成我们需要的完整日期区间，再和用户表做左连接来统计数量。

下面分两种MySQL版本给出具体方案：

方案1：MySQL 8.0及以上版本（支持递归CTE） #

递归公共表表达式（CTE）可以很方便地生成连续日期序列，代码如下：

WITH DateRange AS (
    -- 起始日期
    SELECT DATE('2018-05-01') AS RegistrationDate
    UNION ALL
    -- 递归生成后续日期，直到结束日期前一天
    SELECT DATE_ADD(RegistrationDate, INTERVAL 1 DAY)
    FROM DateRange
    WHERE RegistrationDate < DATE('2018-05-13')
)
SELECT 
    dr.RegistrationDate,
    -- 左连接后，无注册记录的日期对应u.id为NULL，COUNT不会统计NULL，所以返回0
    COUNT(u.id) AS NumberOfRegistrations
FROM DateRange dr
-- 左连接用户表，关联条件是日期匹配
LEFT JOIN Users u ON DATE(u.`date`) = dr.RegistrationDate
GROUP BY dr.RegistrationDate
ORDER BY dr.RegistrationDate;

方案2：MySQL 5.x版本（不支持CTE） #

如果你的MySQL版本较低，没法用CTE，可以手动生成一个数字辅助表来创建日期序列：

SELECT 
    dates.RegistrationDate,
    COUNT(u.id) AS NumberOfRegistrations
FROM (
    -- 生成1到13的数字，对应13天的日期偏移量
    SELECT DATE_ADD('2018-05-01', INTERVAL (n-1) DAY) AS RegistrationDate
    FROM (
        SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
        SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
        SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13
    ) numbers
) dates
LEFT JOIN Users u ON DATE(u.`date`) = dates.RegistrationDate
GROUP BY dates.RegistrationDate
ORDER BY dates.RegistrationDate;

几个关键注意点： #

• 日期格式建议用'2018-05-01'这种标准格式，比'2018/05/1'更稳妥，避免MySQL解析出错。
• 必须用COUNT(u.id)而不是COUNT(*)：左连接后无注册记录的行中，u.id是NULL，COUNT(NULL)不会计入统计，刚好得到0；如果用COUNT(*)，会把左连接生成的空行也算作1，结果就不对了。

内容的提问来源于stack exchange，提问作者Eclixal