如何让MySQL查询返回项目周期内无数据月份的0值统计

我有两张业务表：

• items_header：存储带创建日期的工单，通过progetto字段关联项目ID
• progetti：存储项目的起止日期，其中项目1的周期为2022-01-01至2023-06-30

当前使用的SQL仅能返回存在工单的月份统计：

SELECT DATE_FORMAT(created_on, '%m-%Y') as 'period', COUNT(items_header.id) as 'total' 
FROM items_header JOIN progetti ON items_header.progetto=progetti.id 
WHERE progetto=1
GROUP BY DATE_FORMAT(created_on, '%m-%Y');

现有查询结果：

但我需要返回项目1全周期内的所有月份，无工单创建的月份total字段显示为0，预期结果如下：

附progetti表结构及数据：

核心逻辑是先生成项目全周期内的完整月份序列，再与工单统计结果做左关联，确保所有月份都被保留，无工单的月份用0填充。

方法1：递归CTE生成月份序列（MySQL 8.0+ 适用） #

利用MySQL的递归公共表表达式（CTE）自动生成项目起止日期之间的所有月份：

WITH RECURSIVE month_range AS (
    -- 初始化：取项目1的起始月份第一天和结束日期
    SELECT 
        DATE_FORMAT(start_date, '%Y-%m-01') AS month_start,
        end_date
    FROM progetti
    WHERE id = 1
    UNION ALL
    -- 递归生成后续每个月的第一天
    SELECT 
        DATE_ADD(month_start, INTERVAL 1 MONTH),
        end_date
    FROM month_range
    WHERE DATE_ADD(month_start, INTERVAL 1 MONTH) <= end_date
)
-- 左关联工单表，统计各月工单数量
SELECT 
    DATE_FORMAT(m.month_start, '%m-%Y') AS period,
    COALESCE(COUNT(i.id), 0) AS total
FROM month_range m
LEFT JOIN items_header i 
    ON DATE_FORMAT(i.created_on, '%Y-%m-01') = m.month_start
    AND i.progetto = 1
GROUP BY m.month_start
ORDER BY m.month_start;

方法2：临时月份表（兼容低版本MySQL） #

如果你的MySQL版本不支持递归CTE，可预先生成一个包含足够多月份的临时表，再筛选出项目周期内的月份：

-- 创建临时月份表（这里生成2020-2025年的所有月份，可按需调整范围）
CREATE TEMPORARY TABLE temp_months (
    month_start DATE PRIMARY KEY
);

SET @start_date = '2020-01-01';
WHILE @start_date <= '2025-12-01' DO
    INSERT INTO temp_months VALUES (@start_date);
    SET @start_date = DATE_ADD(@start_date, INTERVAL 1 MONTH);
END WHILE;

-- 关联项目和工单表，统计全周期月份数据
SELECT 
    DATE_FORMAT(t.month_start, '%m-%Y') AS period,
    COALESCE(COUNT(i.id), 0) AS total
FROM temp_months t
JOIN progetti p ON t.month_start BETWEEN DATE_FORMAT(p.start_date, '%Y-%m-01') AND p.end_date
LEFT JOIN items_header i 
    ON DATE_FORMAT(i.created_on, '%Y-%m-01') = t.month_start
    AND i.progetto = 1
WHERE p.id = 1
GROUP BY t.month_start
ORDER BY t.month_start;

关键说明 #

1. 月份序列生成：确保覆盖项目的完整周期，每个月份用当月第一天作为统一匹配标识
2. 左关联：保证所有生成的月份都被保留，不会丢失无工单的月份
3. COALESCE函数：将左关联后无工单对应的NULL值转换为0，符合预期结果格式

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