如何在SQL层级数据中添加分支编号（Leg）列？

需求：在SQL的SELECT语句中新增一列，为层级数据的三个分支分配分支编号。分支编号需在Level 1节点分配，编号顺序无特定要求。注意：实际业务数据使用随机6位ID，示例中为连续编号仅作演示。

数据示例 #

declare @mytable table(id int, parent_id int, level int)

INSERT INTO @MYTABLE VALUES(1, NULL, 0) 
INSERT INTO @MYTABLE VALUES(2, 1, 1) 
INSERT INTO @MYTABLE VALUES(3, 1, 1)
INSERT INTO @MYTABLE VALUES(4, 1, 1) 
INSERT INTO @MYTABLE VALUES(5, 2, 2)
INSERT INTO @MYTABLE VALUES(6, 2, 2) 
INSERT INTO @MYTABLE VALUES(7, 4, 2)
INSERT INTO @MYTABLE VALUES(8, 4, 2) 
INSERT INTO @MYTABLE VALUES(9, 4, 2)
INSERT INTO @MYTABLE VALUES(10, 4, 2) 
INSERT INTO @MYTABLE VALUES(11, 6, 3)
INSERT INTO @MYTABLE VALUES(12, 6, 3) 
INSERT INTO @MYTABLE VALUES(13, 12, 4)
INSERT INTO @MYTABLE VALUES(14, 12, 4) 
INSERT INTO @MYTABLE VALUES(15, 12, 4)
INSERT INTO @MYTABLE VALUES(16, 10, 3) 
INSERT INTO @MYTABLE VALUES(17, 10, 3)
INSERT INTO @MYTABLE VALUES(18, 10, 3) 
INSERT INTO @MYTABLE VALUES(19, 13, 5)
INSERT INTO @MYTABLE VALUES(20, 13, 5) 
INSERT INTO @MYTABLE VALUES(21, 13, 5)
INSERT INTO @MYTABLE VALUES(22, 17, 4) 
INSERT INTO @MYTABLE VALUES(23, 17, 4)
;

层级结构与期望结果 #

• 层级结构：根节点（id=1，level=0）下包含3个Level 1节点（id=2、3、4），每个Level 1节点对应一条独立分支，其所有下属节点均属于该分支。
• 期望结果：同一分支下的所有节点拥有相同的分支编号，编号在Level 1节点分配后向下传递，比如Level 1节点2及其下属分支编号为1，Level 1节点3及其下属为2，Level 1节点4及其下属为3（编号顺序可任意调整）。

解决方案 #

可以通过递归CTE实现分支编号的分配与传递：

;WITH LegAssign AS (
    -- 为Level 1节点分配分支编号
    SELECT 
        id,
        parent_id,
        level,
        ROW_NUMBER() OVER (ORDER BY id) AS leg_number
    FROM @mytable
    WHERE level = 1

    UNION ALL

    -- 递归向下传递分支编号给下属节点
    SELECT 
        m.id,
        m.parent_id,
        m.level,
        la.leg_number
    FROM @mytable m
    JOIN LegAssign la ON m.parent_id = la.id
    WHERE m.level > 1
)
-- 合并根节点与分支节点数据
SELECT 
    m.id,
    m.parent_id,
    m.level,
    CASE WHEN m.level = 0 THEN NULL ELSE la.leg_number END AS leg_number
FROM @mytable m
LEFT JOIN LegAssign la ON m.id = la.id
ORDER BY m.id;

说明 #

1. 首先通过ROW_NUMBER()为所有Level 1节点生成唯一的分支编号，排序字段可根据实际需求调整（比如ORDER BY NEWID()实现随机编号）。
2. 利用递归CTE将分支编号传递给该Level 1节点下的所有子节点、孙节点等。
3. 最后通过左连接合并根节点数据，根节点无分支编号，显示为NULL。

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