如何通过存储过程获取根节点直接子节点作为指定节点的顶层父节点？

表结构与测试数据 #

表Label结构：

• IdLabel：int类型，节点ID
• IdParentLabel：int类型，父节点ID
• Name：varchar(30)类型，节点名称

测试数据插入语句：

Insert Into Label Values (1, null, 'root')
Insert Into Label Values (2, 1, 'child1')
Insert Into Label Values (3, 1, 'child2')
Insert Into Label Values (4, 1, 'child3')
Insert Into Label Values (5, 2, 'grandchild1')
Insert Into Label Values (6, 3, 'grandchild2') 
Insert Into Label Values (7, 4, 'grandchild3') 
Insert Into Label Values (8, 5, 'grandgrandchild1') 
Insert Into Label Values (9, 5, 'grandgrandchild2') 

需求说明 #

编写一个存储过程，接收@IdLabel作为输入参数，返回指定节点的顶层父节点的IdLabel。其中顶层父节点定义为：该节点所在分支中，根节点（Id=1）的直接子节点，根节点永远不被视为顶层父节点。

预期示例 #

• Exec CheckParent 8 应返回2（2是根节点的直接子节点，为8所在分支的顶层父节点）
• Exec CheckParent 9 应返回2
• Exec CheckParent 6 应返回3

当前存储过程问题 #

现有存储过程总是返回根节点（Id=1），不符合预期，代码如下：

CREATE PROCEDURE CheckParent
    @IdLabel int
AS
BEGIN
    DECLARE @TopParent int
    
    SELECT @TopParent = IdParentLabel
    FROM Label
    WHERE IdLabel = @IdLabel
    
    WHILE @TopParent IS NOT NULL AND @TopParent <> 1
    BEGIN
        SET @IdLabel = @TopParent
        
        SELECT @TopParent = IdParentLabel
        FROM Label
        WHERE IdLabel = @IdLabel
    END
    
    IF @TopParent IS NULL
    BEGIN
        SELECT @TopParent = IdLabel
        FROM Label
        WHERE IdParentLabel IS NULL AND IdLabel <> 1
        ORDER BY IdLabel
        OFFSET 0 ROWS
        FETCH NEXT 1 ROWS ONLY
    END
    
    SELECT @TopParent AS TopParent
END

问题分析与修正 #

核心问题 #

原存储过程循环结束后，@TopParent会等于根节点ID（1），但此时的@IdLabel才是需要的顶层父节点——因为@IdLabel的父节点正是根节点。原代码错误地返回了@TopParent（根节点），而非@IdLabel。

修正后的存储过程 #

CREATE PROCEDURE CheckParent
    @IdLabel int
AS
BEGIN
    DECLARE @TopParent int
    
    SELECT @TopParent = IdParentLabel
    FROM Label
    WHERE IdLabel = @IdLabel
    
    WHILE @TopParent IS NOT NULL AND @TopParent <> 1
    BEGIN
        SET @IdLabel = @TopParent
        SELECT @TopParent = IdParentLabel
        FROM Label
        WHERE IdLabel = @IdLabel
    END
    
    -- 循环结束后，判断父节点是否为根节点
    IF @TopParent = 1
    BEGIN
        -- 当前@IdLabel就是根节点的直接子节点，返回它
        SELECT @IdLabel AS TopParent
    END
    ELSE
    BEGIN
        -- 处理特殊情况：输入节点是根节点或无父节点的非根节点，返回null（可根据需求调整）
        SELECT NULL AS TopParent
    END
END

逻辑说明 #

1. 初始获取输入节点的父节点
2. 循环向上查找父节点，直到父节点为根节点（Id=1）或无父节点
3. 当父节点是根节点时，当前的@IdLabel就是目标顶层父节点，返回它
4. 特殊情况处理：如果输入的是根节点或无父节点的非根节点，返回null（可根据实际需求调整返回值）

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