MS SQL Server 实现含起止日期表生成日期区间内每日记录问询

实现思路 #

通过生成连续的日期序列，和原表做关联，筛选出落在每条记录start、end日期区间内的日期即可，两种常用实现方式如下：

方案1：递归CTE实现（无需额外辅助表，适合小数据量场景） #

WITH DateSeries AS (
    -- 递归锚点：取所有记录的最早开始日期作为序列起点
    SELECT MIN(start_date) AS date_val
    FROM your_table
    UNION ALL
    -- 递归累加1天，直到覆盖所有记录的最晚结束日期
    SELECT DATEADD(DAY, 1, date_val)
    FROM DateSeries
    WHERE date_val < (SELECT MAX(end_date) FROM your_table)
)
SELECT 
    t.*,
    ds.date_val AS daily_date
FROM your_table t
INNER JOIN DateSeries ds 
    ON ds.date_val BETWEEN t.start_date AND t.end_date
-- 日期跨度超过100天时需加下面这句调整递归深度，0代表不限制递归深度
OPTION (MAXRECURSION 0);

方案2：数字辅助表实现（性能更高，适合大数据量/高频查询场景） #

先预生成存储连续数字的Tally辅助表（仅需生成一次，后续可重复使用）：

CREATE TABLE Tally (n INT PRIMARY KEY);
WITH 
L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
L1 AS (SELECT 1 AS c FROM L0 a CROSS JOIN L0 b),
L2 AS (SELECT 1 AS c FROM L1 a CROSS JOIN L1 b),
L3 AS (SELECT 1 AS c FROM L2 a CROSS JOIN L2 b),
L4 AS (SELECT 1 AS c FROM L3 a CROSS JOIN L3 b),
L5 AS (SELECT 1 AS c FROM L4 a CROSS JOIN L4 b),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS n FROM L5)
INSERT INTO Tally(n)
SELECT TOP 100000 n FROM Nums;

使用辅助表完成查询：

SELECT 
    t.*,
    DATEADD(DAY, n.n, t.start_date) AS daily_date
FROM your_table t
INNER JOIN Tally n 
    ON n.n <= DATEDIFF(DAY, t.start_date, t.end_date);

注意事项 #

• 代码中your_table替换为你的实际表名，start_date、end_date替换为你的实际起止日期字段名
• 不需要保留原表所有字段时，自行修改SELECT部分的字段列表即可
• 生产环境优先推荐方案2，无递归深度限制，查询性能比递归CTE高3~10倍

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