如何统计指定月份所有日期的学生每日注册记录数？

我懂这种头疼的情况——明明要统计整月的每日数据，但直接查student_registration表只能拿到有注册记录的日期，空日期根本不显示，更别说计数为0了。核心问题在于原表没有存储空日期的记录，所以我们得先自己生成目标月份的所有日期序列，再和原表关联统计。

下面给你两种实用方案，适配不同的MySQL版本：

方案1：MySQL 8.0+ 用递归CTE生成日期序列 #

MySQL 8.0及以上支持递归公共表表达式（CTE），可以很方便生成整月的日期列表：

WITH RECURSIVE date_range AS (
    -- 初始化：指定目标月份的第一天
    SELECT DATE('2018-03-01') AS reg_date
    UNION ALL
    -- 递归生成下一天，直到当月最后一天
    SELECT DATE_ADD(reg_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE reg_date < LAST_DAY('2018-03-01')
)
-- 左连接原表，统计每日注册数
SELECT
    dr.reg_date,
    COUNT(sr.id) AS registration_count
FROM date_range dr
LEFT JOIN student_registration sr ON dr.reg_date = sr.reg_date
GROUP BY dr.reg_date
ORDER BY dr.reg_date;

代码解释： #

• RECURSIVE date_range：递归生成2018年3月的所有日期，从1号到当月最后一天（LAST_DAY函数自动获取当月最后一天）。
• LEFT JOIN：确保所有生成的日期都被保留，即使原表没有对应日期的记录。
• COUNT(sr.id)：因为左连接中空记录的id为NULL，COUNT会自动忽略NULL，所以无注册的日期计数为0。

方案2：MySQL 5.x 版本（无CTE支持）用数字辅助表生成日期 #

如果你的MySQL版本低于8.0，没法用递归CTE，可以用一个临时的数字表来生成日期：

-- 生成0-31的数字（覆盖最多31天的月份）
SELECT
    DATE_ADD('2018-03-01', INTERVAL n.n DAY) AS reg_date,
    COUNT(sr.id) AS registration_count
FROM (
    SELECT 0 AS n UNION ALL SELECT 1 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 UNION ALL SELECT 14
    UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
    UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
    UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29
    UNION ALL SELECT 30 UNION ALL SELECT 31
) n
-- 过滤出当月的日期
LEFT JOIN student_registration sr ON DATE_ADD('2018-03-01', INTERVAL n.n DAY) = sr.reg_date
WHERE DATE_ADD('2018-03-01', INTERVAL n.n DAY) <= LAST_DAY('2018-03-01')
GROUP BY reg_date
ORDER BY reg_date;

代码解释： #

• 子查询n生成了0到31的数字，用来和当月第一天相加得到所有可能的日期。
• WHERE条件过滤掉超出当月最后一天的日期（比如3月只有31天，所以31+1=4月1日会被排除）。
• 同样用LEFT JOIN和COUNT(sr.id)来得到每日的注册数，无注册的日期计数为0。

运行上面的任意一种方案，你都会得到2018年3月1日到31日的完整日期列表，其中没有注册的日期对应的registration_count会显示为0，完全符合你的需求。

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