MySQL如何统计近7个月（含当月）每月观测数，无数据月份补0？

核心思路是先生成最近7个自然月的连续月份序列，再将序列与观测表左关联统计，即可保留所有月份，无数据的月份统计值自动为0。

MySQL 8.0及以上版本解法（推荐） #

使用递归CTE生成连续月份序列，代码如下：

WITH RECURSIVE months AS (
    -- 生成当月起始时间作为基准值
    SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01') AS month_start, 
           YEAR(CURDATE()) AS year_num, 
           MONTH(CURDATE()) AS month_num
    UNION ALL
    -- 递归向前推导6个月，共生成7条月份记录
    SELECT DATE_SUB(month_start, INTERVAL 1 MONTH),
           YEAR(DATE_SUB(month_start, INTERVAL 1 MONTH)),
           MONTH(DATE_SUB(month_start, INTERVAL 1 MONTH))
    FROM months
    WHERE month_start > DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 6 MONTH)
)
SELECT 
    m.year_num,
    m.month_num,
    COUNT(o.observation) AS observ
FROM months m
LEFT JOIN observations o 
    ON DATE_FORMAT(o.created_at, '%Y-%m-01') = m.month_start
    AND o.created_at >= DATE_ADD(NOW(), INTERVAL -6 MONTH)
GROUP BY m.year_num, m.month_num
ORDER BY m.year_num ASC, m.month_num ASC;

MySQL 5.x版本解法（不支持递归CTE场景） #

通过手动构造数字序列生成月份范围，代码如下：

SELECT 
    YEAR(DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL nums.n MONTH)) AS year_num,
    MONTH(DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL nums.n MONTH)) AS month_num,
    COUNT(o.observation) AS observ
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
) nums
LEFT JOIN observations o 
    ON o.created_at >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL nums.n MONTH)
    AND o.created_at < DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL nums.n - 1 MONTH)
GROUP BY nums.n, year_num, month_num
ORDER BY year_num ASC, month_num ASC;

注意事项 #

• 你原有查询存在一个隐藏问题：仅按月份分组未关联年份，跨年度统计时会将不同年份的同一月份数据合并，导致统计错误，上述解法已新增年份维度避免该问题。
• 若不需要展示年份，可在最终查询字段中去掉year_num即可，不影响统计逻辑正确性。

内容的提问来源于stack exchange，提问作者Arley Steven Lache Rueda