如何在SELECT查询结果中补全缺失的日期？

核心思路是先生成查询覆盖的所有日期序列，再通过左连接关联销售表，最后将聚合结果的NULL值转为0，具体实现分不同数据库：

MySQL（8.0及以上版本） #

使用递归CTE生成日期范围：

WITH date_range AS (
    SELECT '2024-03-11' AS day
    UNION ALL
    SELECT DATE_ADD(day, INTERVAL 1 DAY)
    FROM date_range
    WHERE day < '2024-03-13'
)
SELECT 
    dr.day,
    COALESCE(SUM(s.car), 0) AS cars_sold,
    COALESCE(SUM(s.truck), 0) AS trucks_sold,
    COALESCE(COUNT(s.day), 0) AS sales_interactions
FROM date_range dr
LEFT JOIN sales s ON dr.day = s.day
GROUP BY dr.day
ORDER BY dr.day;

PostgreSQL #

利用generate_series函数快速生成日期序列：

SELECT 
    dr.day::DATE,
    COALESCE(SUM(s.car), 0) AS cars_sold,
    COALESCE(SUM(s.truck), 0) AS trucks_sold,
    COALESCE(COUNT(s.day), 0) AS sales_interactions
FROM generate_series('2024-03-11'::DATE, '2024-03-13'::DATE, '1 day'::INTERVAL) dr(day)
LEFT JOIN sales s ON dr.day::DATE = s.day
GROUP BY dr.day::DATE
ORDER BY dr.day::DATE;

SQL Server #

通过递归CTE生成日期范围，注意开启MAXRECURSION：

WITH date_range AS (
    SELECT CAST('2024-03-11' AS DATE) AS day
    UNION ALL
    SELECT DATEADD(DAY, 1, day)
    FROM date_range
    WHERE day < CAST('2024-03-13' AS DATE)
)
SELECT 
    dr.day,
    COALESCE(SUM(s.car), 0) AS cars_sold,
    COALESCE(SUM(s.truck), 0) AS trucks_sold,
    COALESCE(COUNT(s.day), 0) AS sales_interactions
FROM date_range dr
LEFT JOIN sales s ON dr.day = s.day
GROUP BY dr.day
ORDER BY dr.day
OPTION (MAXRECURSION 0);

关键说明 #

• 生成日期序列：确保查询时间范围内的每一天都被包含，不管销售表中有没有对应数据；
• 左连接：保留日期序列中的所有日期，即使销售表无匹配记录；
• COALESCE：将聚合后产生的NULL值替换为0，实现缺失日期的0值展示。

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