基于数值间隔为新行填充历史有效记录信息

要生成任意大小数值间隔的缺失行，核心是为每个间隔区间生成连续的数值序列，这里用递归CTE实现，无需依赖额外数字表，适配所有间隔场景。

原数据（#rowCreate） #

完整SQL脚本 #

WITH GapRecords AS (
    -- 获取每条记录的下一个count值，定位存在间隔的记录
    SELECT 
        parentID,
        name,
        count AS CurrentCount,
        LEAD(count) OVER (PARTITION BY parentID ORDER BY count) AS NextCount
    FROM #rowCreate
),
MissingRows AS (
    -- 递归起始：生成每个间隔的第一个缺失值
    SELECT 
        parentID,
        name,
        CurrentCount + 1 AS MissingCount
    FROM GapRecords
    WHERE NextCount IS NOT NULL AND NextCount > CurrentCount + 1
    UNION ALL
    -- 递归生成后续缺失值，直到覆盖整个间隔区间
    SELECT 
        mr.parentID,
        mr.name,
        mr.MissingCount + 1
    FROM MissingRows mr
    JOIN GapRecords gr 
        ON mr.parentID = gr.parentID 
        AND mr.name = gr.name 
        AND mr.MissingCount + 1 < gr.NextCount
)
-- 输出所有缺失行（如需插入临时表，替换为INSERT INTO #rowCreate (...) SELECT ...）
SELECT parentID, name, MissingCount AS count
FROM MissingRows
ORDER BY parentID, count
OPTION (MAXRECURSION 0); -- 取消递归深度限制，适配超大间隔场景

脚本说明 #

1. GapRecords CTE：通过LEAD窗口函数，获取同parentID下当前记录的下一条count值，筛选出NextCount > CurrentCount + 1的记录（即存在间隔的记录）。
2. MissingRows CTE：
   • 起始分支：生成每个间隔的第一个缺失值（CurrentCount + 1）。
   • 递归分支：在上一个缺失值基础上加1，直到数值达到NextCount - 1，覆盖整个间隔区间。
3. 最终输出的结果就是你需要的所有缺失行，排序后与预期完全一致。

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