MySQL近12个月查询：需包含无数据月份（值为NULL）

我懂你遇到的痛点了——直接从vacancies表统计近12个月的订阅数据时，没有订阅记录的月份会直接从结果里消失，导致数据不连贯。要解决这个问题，核心思路是先生成一个包含近12个月所有月份的完整列表，再和业务表做关联统计，这样空月份也能被保留下来。

先回顾下你的表结构（简化后）：

具体解决方案 #

下面提供两种适配不同数据库的实现方式，你可以根据自己的数据库类型选择：

方式一：递归CTE生成月份列表（适用于MySQL 8.0+、PostgreSQL、SQL Server）

这种方式利用递归公共表表达式（CTE）自动生成近12个月的月份数据，代码更简洁：

WITH months AS (
    -- 生成起始月份：当前月份往前推11个月
    SELECT DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '11 months' AS month_date
    UNION ALL
    -- 递归生成后续每个月份，直到当前月份
    SELECT month_date + INTERVAL '1 month'
    FROM months
    WHERE month_date < DATE_TRUNC('month', CURRENT_DATE)
)
SELECT
    TO_CHAR(m.month_date, 'YYYY-MM') AS month,
    COUNT(v.vacancy_id) AS subscription_count
FROM months m
-- 左关联业务表，确保空月份不丢失
LEFT JOIN vacancies v
    ON DATE_TRUNC('month', v.create_time) = m.month_date
    AND v.is_deleted = 0  -- 过滤已删除的订阅记录
GROUP BY m.month_date
ORDER BY m.month_date;

方式二：数字表生成月份列表（适用于不支持CTE的老版本数据库）

如果你的数据库不支持递归CTE，可以手动生成12个月份的列表，再做关联：

SELECT
    DATE_FORMAT(m.month_date, '%Y-%m') AS month,
    COUNT(v.vacancy_id) AS subscription_count
FROM (
    -- 生成近12个月的日期列表
    SELECT DATE_SUB(CURDATE(), INTERVAL 11 MONTH) + INTERVAL (a.a) MONTH AS month_date
    FROM (
        SELECT 0 AS a 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 UNION ALL SELECT 10 UNION ALL SELECT 11
    ) AS a
) m
LEFT JOIN vacancies v
    ON DATE_FORMAT(v.create_time, '%Y-%m') = DATE_FORMAT(m.month_date, '%Y-%m')
    AND v.is_deleted = 0
GROUP BY m.month_date
ORDER BY m.month_date;

关键细节说明 #

• LEFT JOIN的作用：确保月份列表里的每个月份都会出现在结果中，即使业务表没有对应数据，统计数会显示为0。
• 日期匹配逻辑：把create_time和生成的月份日期统一格式（截断到月份），保证关联时能精准匹配。
• 过滤条件位置：把is_deleted = 0放在ON子句而不是WHERE子句，避免过滤掉空月份的记录。

这样处理后，你的查询结果就会包含近12个月的所有月份，哪怕某个月份没有订阅记录，也会清晰显示该月份和0的统计数。

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