如何让MySQL返回过去10个月的月度统计（无数据月份值为0）

嘿，这个需求我之前处理过！要让MySQL返回没有数据的月份的0值，关键是先生成连续的10个月日期序列，再和你的Activities表做左连接——这样就能把原本缺失的月份补全，并用COALESCE把空值转换成0。下面给你两种实用方案：

方案1：用递归CTE（MySQL 8.0+推荐） #

MySQL 8.0及以上支持递归公共表表达式（CTE），可以轻松生成连续的月份序列：

WITH RECURSIVE month_range AS (
    -- 起始：取10个月前的当月第一天，生成第一个月份
    SELECT 
        DATE_FORMAT(ADDDATE(NOW(), INTERVAL -10 MONTH), '%Y-%m-01') AS month_start,
        1 AS counter
    UNION ALL
    -- 递归生成后续9个月份，总共10个
    SELECT 
        DATE_FORMAT(ADDDATE(month_start, INTERVAL 1 MONTH), '%Y-%m-01'),
        counter + 1
    FROM month_range
    WHERE counter < 10
)
SELECT 
    COALESCE(SUM(a.Distance), 0) AS `Data`,
    MONTH(m.month_start) AS `DateVal`,
    YEAR(m.month_start) AS `YearVal` -- 建议加上年份，避免跨年时月份混淆
FROM month_range m
-- 左连接活动表，按年月匹配
LEFT JOIN `Activities` a 
    ON DATE_FORMAT(a.`Date`, '%Y-%m') = DATE_FORMAT(m.month_start, '%Y-%m')
GROUP BY m.month_start
ORDER BY m.month_start DESC;

说明： #

• 递归CTE先生成过去10个月的每月第一天，确保序列连续
• 左连接后，没有对应数据的月份会返回NULL，用COALESCE把它转成0
• 加上YearVal是为了避免跨年场景（比如去年12月和今年12月）的分组错误

方案2：兼容MySQL 5.x的数字表法 #

如果你的MySQL版本低于8.0，不支持CTE，可以用手动生成的数字序列来创建月份范围：

SELECT 
    COALESCE(SUM(a.Distance), 0) AS `Data`,
    MONTH(m.month_start) AS `DateVal`,
    YEAR(m.month_start) AS `YearVal`
FROM (
    -- 生成0-9的数字，用来计算10个不同的月份
    SELECT 
        DATE_FORMAT(ADDDATE(NOW(), INTERVAL -10 + t.n MONTH), '%Y-%m-01') AS month_start
    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
    ) t
) m
LEFT JOIN `Activities` a 
    ON DATE_FORMAT(a.`Date`, '%Y-%m') = DATE_FORMAT(m.month_start, '%Y-%m')
GROUP BY m.month_start
ORDER BY m.month_start DESC;

说明： #

• 子查询t生成0到9的数字，每个数字对应一个偏移月份
• 通过ADDDATE计算出过去10个月的每月第一天，得到连续序列
• 后续逻辑和方案1一致，左连接后补0

额外注意点 #

• 确保Activities表的Date字段是日期/日期时间类型，否则DATE_FORMAT会出错
• 排序时用生成的month_start字段，而不是原表的Date，保证月份顺序绝对正确
• 如果你的“过去10个月”定义是从当前月往前数10个（比如当前5月，取去年8月到今年5月），可以调整ADDDATE的偏移量，比如把INTERVAL -10 MONTH改成INTERVAL -9 MONTH

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