SQL Server同表存储父子层级关系时如何将层级值写入新表

性能优化前提：先加必要索引 #

你的原写法卡顿核心原因是隐式笛卡尔积关联+多次分批次写入目标表，22.5万数据场景下全表扫描+重复IO开销会被无限放大，首先需要给源表加覆盖索引避免回表：

-- 源表加覆盖索引，递归关联时直接走索引查找，不需要回表查数据
CREATE NONCLUSTERED INDEX IX_Table1_Parent_IncludeChild ON TABLE1(Parent) INCLUDE(Child);

-- 提前建好目标表，避免写入过程中做DDL操作
CREATE TABLE TABLE2 (
    Parent INT,
    Child1 INT,
    Child2 INT,
    Child3 INT,
    Child4 INT
);

高性能一次性写入方案 #

用递归CTE一次性计算所有层级，不需要分批次更新目标表，全程只需要读1次源表、写1次目标表，22.5万数据正常几秒就能跑完：

WITH RecursiveHierarchy AS (
    -- 锚点层：取第一级父子关系，对应Child1
    SELECT 
        t.Parent,
        t.Child AS CurrentNode,
        1 AS HierarchyLevel,
        t.Child AS LastLeafNode
    FROM TABLE1 t

    UNION ALL

    -- 递归层：关联下一级子节点，最多递归到第4级（可按实际需求调整上限）
    SELECT 
        rh.Parent,
        t.Child AS CurrentNode,
        rh.HierarchyLevel + 1 AS HierarchyLevel,
        t.Child AS LastLeafNode
    FROM RecursiveHierarchy rh
    INNER JOIN TABLE1 t ON rh.CurrentNode = t.Parent
    WHERE rh.HierarchyLevel < 4
),
FullHierarchy AS (
    -- 行转列生成宽表结构，空层级自动补全最后一个叶子节点值，匹配你示例的输出规则
    SELECT 
        Parent,
        MAX(CASE WHEN HierarchyLevel = 1 THEN CurrentNode ELSE LastLeafNode END) AS Child1,
        MAX(CASE WHEN HierarchyLevel = 2 THEN CurrentNode ELSE LastLeafNode END) AS Child2,
        MAX(CASE WHEN HierarchyLevel = 3 THEN CurrentNode ELSE LastLeafNode END) AS Child3,
        MAX(CASE WHEN HierarchyLevel = 4 THEN CurrentNode ELSE LastLeafNode END) AS Child4
    FROM RecursiveHierarchy
    GROUP BY Parent
)
-- 一次性写入目标表
INSERT INTO TABLE2 (Parent, Child1, Child2, Child3, Child4)
SELECT Parent, Child1, Child2, Child3, Child4
FROM FullHierarchy
-- 如果你的业务层级超过100，开启无限制递归配置
OPTION (MAXRECURSION 0);

适配调整说明 #

如果实际业务层级超过4级，只需要修改两个位置即可：

• 递归层的WHERE rh.HierarchyLevel < 4里的数值改为你的最大层级数
• 行转列的CASE WHEN部分增加对应层级的判断逻辑

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