SQL Server如何高效实现按日期区间拆分重复记录（无需游标）

对于百万级数据量、1-10天动态时间间隔的场景，绝对不要用游标——这会让你的查询慢到离谱。SQL Server最擅长集合操作，咱们用Tally Table（数字表）来实现，性能拉满。

最优方案：使用Tally Table进行集合连接 #

Tally Table就是一个包含连续整数的表，咱们用它来“生成”需要重复的次数，然后和源表做连接，一次搞定所有记录的重复。

步骤1：生成临时Tally Table（快速上手） #

如果只是临时用，直接用CTE生成1到10的连续数字（覆盖最大10天的间隔）：

WITH Tally AS (
    -- 生成1到10的连续整数，sys.all_columns是系统表，用来凑足够的行
    SELECT TOP (10) 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_columns ac1
    CROSS JOIN sys.all_columns ac2
)
SELECT 
    s.*,
    -- 计算每条重复记录对应的日期
    DATEADD(day, t.n - 1, s.start_time) AS repeated_date
FROM YourSourceTable s
-- 连接条件：数字n不超过日期间隔的总天数（含首尾）
JOIN Tally t 
    ON t.n <= DATEDIFF(day, s.start_time, s.end_time) + 1
ORDER BY s.id, repeated_date;

步骤2：创建永久Tally Table（优化百万级性能） #

如果要频繁用这个逻辑，建议建一个带索引的永久Tally Table，性能会更优：

-- 创建永久数字表
CREATE TABLE dbo.Tally (n INT PRIMARY KEY);

-- 插入1到100的连续整数（预留足够空间，以防以后间隔变大）
INSERT INTO dbo.Tally (n)
SELECT TOP (100) 
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2;

之后查询直接用这个表连接就行，主键索引会让连接速度飞快：

SELECT 
    s.*,
    DATEADD(day, t.n - 1, s.start_time) AS repeated_date
FROM YourSourceTable s
JOIN dbo.Tally t 
    ON t.n <= DATEDIFF(day, s.start_time, s.end_time) + 1
ORDER BY s.id, repeated_date;

性能优化小贴士 #

• 给源表加索引：如果start_time和end_time经常用来计算间隔，给这两个字段建联合索引，或者新增一个持久化计算列：

  -- 新增持久化计算列，存储日期间隔的天数
  ALTER TABLE YourSourceTable ADD DaysInterval AS DATEDIFF(day, start_time, end_time) PERSISTED;
  -- 给计算列建索引
  CREATE INDEX IX_SourceTable_DaysInterval ON YourSourceTable(DaysInterval);
  

  之后连接条件可以改成t.n <= s.DaysInterval + 1，能进一步提升查询速度。
• 控制Tally Table的行数：只生成覆盖最大间隔的数字就行（比如这里10行），不要生成多余的行，避免不必要的连接开销。

不推荐的方案：递归CTE #

虽然递归CTE也能实现，但绝对不适合百万级数据——递归是逐行处理，性能比集合操作差几个数量级。如果实在好奇写法，给你参考：

WITH RecursiveCTE AS (
    -- 初始行：源表所有记录，current_date设为start_time
    SELECT 
        s.id,
        s.start_time,
        s.end_time,
        s.other_columns,
        s.start_time AS current_date
    FROM YourSourceTable s
    UNION ALL
    -- 递归生成后续日期
    SELECT 
        r.id,
        r.start_time,
        r.end_time,
        r.other_columns,
        DATEADD(day, 1, r.current_date) AS current_date
    FROM RecursiveCTE r
    WHERE r.current_date < r.end_time
)
SELECT * FROM RecursiveCTE
ORDER BY id, current_date
OPTION (MAXRECURSION 0); -- 关闭递归次数限制（因为最大间隔只有10天，也可以设为10）

但再次强调：大数据量下别用这个，慢到你怀疑人生。

为什么游标不行？ #

游标是逐行读取、逐行处理，百万级数据下会产生大量的IO和上下文切换，性能差到爆炸。集合操作是SQL Server的强项，能利用并行执行、索引优化等特性，速度是游标几十甚至上百倍。

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