MySQL如何补全日期范围内缺失的月份并将统计值设为0？

要补全指定日期范围内缺失的月份并将统计值设为0，核心思路是先生成该范围内的所有连续月份序列，再与原统计结果做左连接，这样没有数据的月份就能自动填充0。以下是适配你需求的完整查询（适用于MySQL 8.0及以上版本，支持递归CTE）：

WITH RECURSIVE month_range AS (
    -- 起始月份
    SELECT 
        DATE('2021-09-01') AS month_start
    UNION ALL
    -- 递归生成后续月份，直到结束日期
    SELECT 
        DATE_ADD(month_start, INTERVAL 1 MONTH)
    FROM month_range
    WHERE month_start < DATE('2023-01-01')
)
SELECT 
    MONTH(m.month_start) AS month,
    YEAR(m.month_start) AS year,
    COALESCE(d.Total, 0) AS Total
FROM month_range m
LEFT JOIN (
    -- 原统计查询
    SELECT 
        MONTH(timestamp) AS month,
        YEAR(timestamp) AS year,
        COUNT(*) AS Total
    FROM dialogs
    WHERE timestamp BETWEEN '2021-09-01' AND '2023-01-01'
    GROUP BY YEAR(timestamp), MONTH(timestamp)
) d ON m.month_start = DATE_FORMAT(CONCAT(d.year, '-', d.month, '-01'), '%Y-%m-%d')
ORDER BY year, month;

代码说明： #

1. 递归CTE month_range：生成从2021-09-01到2023-01-01的每个月第一天，确保覆盖范围内所有月份。
2. 左连接原统计结果：将生成的连续月份与原查询的统计结果关联，匹配不上的月份（即缺失数据的月份）会返回NULL。
3. COALESCE函数：将NULL的统计值替换为0，达到填充缺失月份的需求。

执行该查询后，就能得到包含指定范围内所有月份的结果，缺失数据的月份Total会显示为0，完全符合你的期望输出。

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