MySQL生成指定区间连续日期与结果表UNION补全缺失日期统计值

你写的SQL存在两个致命问题，导致无法得到预期结果：

• 日期筛选逻辑写反：起始日期'2022-06-10'被放在<=判断后，结束日期'2022-06-17'被放在>=判断后，逻辑上要求记录日期同时早于更早的起始日、晚于更晚的结束日，本身就查不出符合条件的业务数据。
• 补0逻辑仅覆盖单天：UNION部分只硬编码了当前日期减1天的单条0值记录，没有生成指定起止区间内的全量连续日期，自然无法为所有无业务数据的日期补0。

直接用递归CTE生成指定区间的连续日期序列，再左关联业务聚合结果，关联不到的日期将total置为0即可，不需要用UNION手动拼接零散日期。

-- 传入你需要查询的起止日期参数即可
WITH RECURSIVE date_series AS (
    -- 起始日期
    SELECT DATE('2022-06-10') AS stat_date
    UNION ALL
    -- 逐天生成日期，直到达到结束日期
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY)
    FROM date_series
    WHERE stat_date < DATE('2022-06-17')
),
biz_agg AS (
    SELECT
        SUM(amount) AS total,
        DATE(created_date) AS stat_date
    FROM quotation
    WHERE created_date >= '2022-06-10'
      -- 兼容created_date带时分秒的场景，避免漏掉结束日当天的晚些时候的数据
      AND created_date < DATE_ADD('2022-06-17', INTERVAL 1 DAY)
    GROUP BY DATE(created_date)
)
SELECT
    -- 关联不到业务数据时，total返回0
    COALESCE(b.total, 0) AS total,
    d.stat_date AS date
FROM date_series d
LEFT JOIN biz_agg b ON d.stat_date = b.stat_date
ORDER BY d.stat_date;

注意点：

• 不要用DATE_FORMAT做分组和排序的依据，直接用DATE类型字段排序性能更好，也不会出现跨月、跨年的排序错乱问题
• COALESCE函数会按顺序返回第一个非null值，刚好适配左关联无数据时total为null的场景

5.x版本不支持递归CTE，最通用的方案是提前建一张日历辅助表，预存足够长时间范围的日期，查询时直接关联日历表即可，一次建表后续所有日期补0的需求都能复用。

1. 建表并预填充日期数据

-- 建日历主键表
CREATE TABLE IF NOT EXISTS dim_calendar (
    calendar_date DATE PRIMARY KEY
);

-- 一次性填充2020-2030年的所有日期，范围可按需调整
INSERT IGNORE INTO dim_calendar(calendar_date)
SELECT DATE_ADD('2020-01-01', INTERVAL (a.num + 10*b.num + 100*c.num) DAY) AS gen_date
FROM (SELECT 0 num 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) a
CROSS JOIN (SELECT 0 num 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) b
CROSS JOIN (SELECT 0 num 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) c
HAVING gen_date BETWEEN '2020-01-01' AND '2030-12-31';

2. 业务查询直接关联日历表

SELECT
    COALESCE(SUM(q.amount), 0) AS total,
    c.calendar_date AS date
FROM dim_calendar c
LEFT JOIN quotation q 
    ON c.calendar_date = DATE(q.created_date)
    AND q.created_date >= '2022-06-10'
    AND q.created_date < DATE_ADD('2022-06-17', INTERVAL 1 DAY)
WHERE c.calendar_date BETWEEN '2022-06-10' AND '2022-06-17'
GROUP BY c.calendar_date
ORDER BY c.calendar_date;

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