如何实现SQL查询返回截至当前所有日期，缺失数据行Amount显示0

现有表TB_TEST存储生产日期和对应产量，建表及插入数据脚本如下：

CREATE TABLE TB_TEST (
    ManDate date,
    Amount float
)    

INSERT INTO TB_Test Values ('2023-Mar-14', 13.0)
INSERT INTO TB_Test Values ('2023-Mar-13', 13.5)
INSERT INTO TB_Test Values ('2023-Mar-10', 12.8)
INSERT INTO TB_Test Values ('2023-Mar-8', 14.6)

当前执行SELECT ManDate, Amount FROM TB_TEST仅返回有生产记录的日期行，需修改查询实现：

• 展示从表中最早生产日期到当前日期的每一天
• 无生产记录的日期，Amount字段显示为0

解决方案 #

核心思路是先生成连续的日期序列，再与原表左连接，将无匹配的Amount替换为0。以下是主流数据库的实现方式：

SQL Server #

使用递归CTE生成日期序列：

WITH DateSequence AS (
    SELECT MIN(ManDate) AS SeqDate
    FROM TB_TEST
    UNION ALL
    SELECT DATEADD(day, 1, SeqDate)
    FROM DateSequence
    WHERE SeqDate < CAST(GETDATE() AS DATE)
)
SELECT 
    ds.SeqDate AS ManDate,
    ISNULL(t.Amount, 0) AS Amount
FROM DateSequence ds
LEFT JOIN TB_TEST t ON ds.SeqDate = t.ManDate
ORDER BY ds.SeqDate DESC;

MySQL 8.0+ #

同样用递归CTE实现：

WITH RECURSIVE DateSequence AS (
    SELECT MIN(ManDate) AS SeqDate
    FROM TB_TEST
    UNION ALL
    SELECT DATE_ADD(SeqDate, INTERVAL 1 DAY)
    FROM DateSequence
    WHERE SeqDate < CURDATE()
)
SELECT 
    ds.SeqDate AS ManDate,
    IFNULL(t.Amount, 0) AS Amount
FROM DateSequence ds
LEFT JOIN TB_TEST t ON ds.SeqDate = t.ManDate
ORDER BY ds.SeqDate DESC;

PostgreSQL #

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

SELECT 
    gs.SeqDate::date AS ManDate,
    COALESCE(t.Amount, 0) AS Amount
FROM generate_series(
    (SELECT MIN(ManDate) FROM TB_TEST),
    CURRENT_DATE,
    INTERVAL '1 day'
) gs(SeqDate)
LEFT JOIN TB_TEST t ON gs.SeqDate::date = t.ManDate
ORDER BY gs.SeqDate DESC;

关键细节 #

• 以表中最早生产日期作为序列起始点，确保覆盖已有数据的时间范围
• 左连接保留所有日期，无生产记录的行Amount会返回NULL，通过函数替换为0
• 按日期倒序排列，方便查看最新数据

内容的提问来源于stack exchange，提问作者Peter Nguy Nguyen