如何统计近7个月数据（含无记录月份的0值统计）

问题描述 #

有一张包含datetime类型列created_date的数据表，需要统计近7个月的记录数，要求即使某月份没有记录，也要显示该月份并将统计数设为0。

表结构与示例数据：

id | created_date
1  | 2022-11-08
2  | 2023-02-12
3  | 2023-03-09
4  | 2023-07-02
5  | 2023-07-07
6  | 2023-07-09

原查询的问题：直接从数据表过滤分组时，没有记录的月份不会被返回，导致输出缺失部分月份。

解决方案 #

核心思路是先生成近7个月的完整月份列表，再通过左连接将统计结果与月份列表关联，确保所有月份都被保留。

方法1：使用CTE生成月份列表（MySQL 8.0+支持） #

WITH months AS (
    -- 生成近7个月的第一个月份（当前日期往前推6个月）
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 6 MONTH), '%Y-%m') AS month_val
    UNION ALL
    -- 递归生成后续月份，直到当前月份
    SELECT DATE_FORMAT(DATE_ADD(month_val, INTERVAL 1 MONTH), '%Y-%m')
    FROM months
    WHERE DATE_ADD(month_val, INTERVAL 1 MONTH) <= DATE_FORMAT(CURDATE(), '%Y-%m')
)
SELECT 
    IFNULL(d.datacount, 0) AS datacount,
    MONTH(m.month_val) AS datamonth,
    m.month_val AS year_month -- 可选，显示年月避免跨年度歧义
FROM months m
-- 左连接统计结果，确保所有月份都被保留
LEFT JOIN (
    SELECT 
        COUNT(id) AS datacount,
        DATE_FORMAT(created_date, '%Y-%m') AS year_month
    FROM data
    WHERE created_date >= DATE_SUB(CURDATE(), INTERVAL 7 MONTH)
    GROUP BY year_month
) d ON m.month_val = d.year_month
ORDER BY m.month_val;

方法2：手动生成月份列表（兼容MySQL 5.x） #

如果不支持CTE，可以用UNION手动列出近7个月：

SELECT 
    IFNULL(d.datacount, 0) AS datacount,
    MONTH(m.month_val) AS datamonth,
    m.month_val AS year_month
FROM (
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 6 MONTH), '%Y-%m') AS month_val
    UNION SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 5 MONTH), '%Y-%m')
    UNION SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 4 MONTH), '%Y-%m')
    UNION SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH), '%Y-%m')
    UNION SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), '%Y-%m')
    UNION SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m')
    UNION SELECT DATE_FORMAT(CURDATE(), '%Y-%m')
) m
LEFT JOIN (
    SELECT 
        COUNT(id) AS datacount,
        DATE_FORMAT(created_date, '%Y-%m') AS year_month
    FROM data
    WHERE created_date >= DATE_SUB(CURDATE(), INTERVAL 7 MONTH)
    GROUP BY year_month
) d ON m.month_val = d.year_month
ORDER BY m.month_val;

输出结果示例 #

假设当前日期为2023-07，执行后会输出完整的近7个月统计：

datacount | datamonth | year_month
0         | 1         | 2023-01
1         | 2         | 2023-02
1         | 3         | 2023-03
0         | 4         | 2023-04
0         | 5         | 2023-05
0         | 6         | 2023-06
3         | 7         | 2023-07

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