如何基于起始日期表生成指定日期范围的SQL日期表

要生成指定日期范围内的完整日期表，并关联原表中最近生效的Value，核心思路是先生成连续日期序列，再为每个日期匹配原表中小于等于该日期的最新记录。以下是主流SQL数据库的具体实现：

假设原表结构与数据 #

先定义原表（假设表名为value_changes）：

CREATE TABLE value_changes (
    date DATE PRIMARY KEY,
    value INT NOT NULL
);

INSERT INTO value_changes (date, value)
VALUES ('2022-01-01', 5), ('2022-07-12', 10), ('2022-11-15', 3);

PostgreSQL 实现 #

利用generate_series快速生成连续日期，再通过LATERAL JOIN关联最近的生效记录：

SELECT 
    dates.dt AS date,
    vc.value
FROM 
    generate_series('2022-01-01'::DATE, '2022-12-01'::DATE, '1 day'::INTERVAL) AS dates(dt)
LEFT JOIN LATERAL (
    SELECT value 
    FROM value_changes 
    WHERE date <= dates.dt 
    ORDER BY date DESC 
    LIMIT 1
) vc ON true
ORDER BY dates.dt;

逻辑说明：

• generate_series直接生成起始到结束的所有日期
• LATERAL JOIN为每个日期单独查询原表中最新的生效记录（按日期倒序取第一条）

SQL Server 实现 #

用递归CTE生成连续日期，再通过OUTER APPLY匹配最近记录：

WITH date_series AS (
    SELECT CAST('2022-01-01' AS DATE) AS dt
    UNION ALL
    SELECT DATEADD(DAY, 1, dt)
    FROM date_series
    WHERE dt < '2022-12-01'
)
SELECT 
    ds.dt AS date,
    vc.value
FROM date_series ds
OUTER APPLY (
    SELECT TOP 1 value
    FROM value_changes
    WHERE date <= ds.dt
    ORDER BY date DESC
) vc
ORDER BY ds.dt
OPTION (MAXRECURSION 0); -- 解除递归层数限制（默认仅支持100层）

逻辑说明：

• 递归CTE从起始日期开始，每日递增1天直到结束日期
• OUTER APPLY为每个日期获取原表中最新的生效Value

MySQL 8.0+ 实现 #

使用递归CTE生成日期序列，再用关联子查询匹配最近记录：

WITH RECURSIVE date_series AS (
    SELECT STR_TO_DATE('2022-01-01', '%Y-%m-%d') AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM date_series
    WHERE dt < STR_TO_DATE('2022-12-01', '%Y-%m-%d')
)
SELECT 
    ds.dt AS date,
    (SELECT value 
     FROM value_changes 
     WHERE date <= ds.dt 
     ORDER BY date DESC 
     LIMIT 1) AS value
FROM date_series ds
ORDER BY ds.dt;

逻辑说明：

• 递归CTE生成连续日期序列
• 关联子查询为每个日期筛选出原表中最新的生效Value

通用兼容方案（适配多数数据库） #

如果你的数据库不支持递归CTE或LATERAL/OUTER APPLY，可以先创建数字辅助表生成日期：

-- 先创建数字表（示例，需包含0到334的数字，覆盖目标日期范围的334天）
CREATE TABLE numbers (n INT PRIMARY KEY);
INSERT INTO numbers (n) VALUES (0),(1),(2),...,(334);

-- 生成日期表并匹配Value
SELECT 
    DATE_ADD('2022-01-01', INTERVAL n DAY) AS date,
    (SELECT value 
     FROM value_changes 
     WHERE date <= DATE_ADD('2022-01-01', INTERVAL n DAY) 
     ORDER BY date DESC 
     LIMIT 1) AS value
FROM numbers
WHERE DATE_ADD('2022-01-01', INTERVAL n DAY) <= '2022-12-01'
ORDER BY date;

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