MySQL查询如何补全过去12个月无职位空缺订阅数据的月份行

刚好碰到过类似的需求，其实核心就是利用你现有的calendar_months表来做“基础月份框架”，把过去12个月的所有月份先列出来，再和实际的订阅统计数据做左连接，这样没数据的月份就会自动补出来，最后把空值转成0就行，具体步骤如下：

1. 先筛选出过去12个月的月份数据 #

首先从你的calendar_months表中，精准定位过去12个月的记录。假设你的month_id是YYYYMM格式（比如202405代表2024年5月），可以用日期函数来筛选：

SELECT month_id, en_long AS month_name
FROM calendar_months
WHERE month_id >= DATE_FORMAT(CURRENT_DATE - INTERVAL 11 MONTH, '%Y%m')
ORDER BY month_id;

这段SQL会返回过去12个月的所有月份，不管有没有订阅数据，这就是我们要的“骨架”。

2. 统计有订阅记录的月份数据 #

接下来，从你的职位空缺订阅表（假设表名为vacancy_subscriptions，日期字段为subscribe_date），按月份分组统计订阅数：

SELECT 
    DATE_FORMAT(subscribe_date, '%Y%m') AS month_id,
    COUNT(*) AS subscription_count
FROM vacancy_subscriptions
WHERE subscribe_date >= CURRENT_DATE - INTERVAL 11 MONTH
GROUP BY month_id;

这里要注意日期范围和第一步保持一致，确保统计的是同一时间段的数据。

3. 左连接并补全0值 #

把上面两个查询用左连接结合起来，用COALESCE()函数把没有订阅数据的月份的subscription_count从NULL转换成0：

SELECT 
    cm.month_id,
    cm.en_long AS month_name,
    COALESCE(sub_stats.subscription_count, 0) AS total_subscriptions
FROM (
    -- 第一步：获取过去12个月的所有月份
    SELECT month_id, en_long
    FROM calendar_months
    WHERE month_id >= DATE_FORMAT(CURRENT_DATE - INTERVAL 11 MONTH, '%Y%m')
) cm
LEFT JOIN (
    -- 第二步：统计有订阅的月份数据
    SELECT 
        DATE_FORMAT(subscribe_date, '%Y%m') AS month_id,
        COUNT(*) AS subscription_count
    FROM vacancy_subscriptions
    WHERE subscribe_date >= CURRENT_DATE - INTERVAL 11 MONTH
    GROUP BY month_id
) sub_stats ON cm.month_id = sub_stats.month_id
ORDER BY cm.month_id;

关键说明： #

• 左连接的作用：确保calendar_months里的所有月份都会出现在结果中，哪怕对应的订阅统计子查询里没有该月份的数据。
• COALESCE()函数：专门用来处理空值，当sub_stats.subscription_count为NULL（也就是该月没有订阅）时，自动替换成0。

如果你的month_id不是YYYYMM格式，或者订阅表的字段名不一样，只需要调整对应的日期转换和字段名称就行，核心逻辑不变。

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