在T-SQL中为日期区间内每一天生成对应行（无需日历表）

问题描述 #

现有表myTable包含SchoolId、StartDate、EndDate、SomeBit、BigId字段，需实现：

• 当某条数据的StartDate与EndDate不同时，为该日期区间内的每一天生成一条记录，除日期外其余字段与原行一致
• 起止日期相同时直接保留原记录
• 禁止使用日历表实现，需处理数十万条数据，使用SSMS中的T-SQL操作

最小可复现示例 #

CREATE TABLE
  myTable (
    [SchoolId] int,
    [StartDate] date,
    [EndDate] date,
    [SomeBit] bit,
    [BigId] bigint,
);

INSERT INTO
  myTable (
    [SchoolId],
    [StartDate],
    [EndDate],
    [SomeBit],
    [BigId]
  )
VALUES
  (1, '20150101', '20150104', 0,  437457324555),
  (2, '20150101', '20150101', 1,  4573467234),
  (3, '20150102', '20150102', 0,  45756654565),
  (4, '20150102', '20150103', 1,  4564576754),
  (5, '20150105', '20150106', 1, 54745753)
;

SELECT * FROM myTable;

解决方案：递归CTE实现 #

使用递归公共表表达式（CTE）逐天生成日期区间内的记录，无需依赖额外日历表：

WITH DateRecursion AS (
    -- 锚点成员：读取原表所有行，初始日期为StartDate
    SELECT 
        SchoolId,
        StartDate AS CurrentDate,
        EndDate,
        SomeBit,
        BigId
    FROM myTable
    UNION ALL
    -- 递归成员：逐天递增日期，直到等于EndDate
    SELECT 
        SchoolId,
        DATEADD(day, 1, CurrentDate) AS CurrentDate,
        EndDate,
        SomeBit,
        BigId
    FROM DateRecursion
    WHERE CurrentDate < EndDate
)
SELECT 
    SchoolId,
    CurrentDate AS Date,
    SomeBit,
    BigId
FROM DateRecursion
ORDER BY SchoolId, CurrentDate
OPTION (MAXRECURSION 0); -- 禁用递归深度限制，支持任意长度的日期区间

关键说明 #

1. 递归逻辑：锚点成员获取原表所有数据，递归成员每次将CurrentDate加1天，直到日期等于EndDate，自动终止递归
2. 递归深度处理：默认T-SQL递归深度限制为100，OPTION (MAXRECURSION 0) 可解除此限制，确保能处理超过100天的长日期区间
3. 性能适配：递归CTE无需创建临时表或日历表，对于数十万条数据的场景，执行效率可满足需求，且逻辑简洁易维护

预期输出 #

• SchoolId=1将生成4条记录，对应2015-01-01至2015-01-04的每一天
• SchoolId=2、3各保留1条原记录
• SchoolId=4、5各生成2条记录，对应各自的日期区间

内容的提问来源于stack exchange，提问作者Marja van der Wind