SQL层级表中获取各节点根父节点ID的问题求助

我看你现在的查询里，GrandParentId列输出的是节点的直接父ID，而不是真正的根节点（最顶层、ParentId为NULL的节点）ID——这就是问题的核心。咱们调整下CTE的逻辑，就能准确拿到每个节点对应的根父节点ID了。

问题出在哪？ #

你的现有CTE里，最后输出的GrandParentId用的是CTE.ParentId，但这个字段是当前节点的直接父级ID，不是根节点ID。虽然看起来部分结果是对的，但逻辑上是依赖递归传递的巧合，我们需要明确在CTE里追踪根节点ID才行。

修改后的SQL代码 #

我们在CTE里新增一个RootModuleId字段，专门存储每个节点对应的根节点ID，递归的时候把这个值一直传递下去：

CREATE TABLE #Modules (
    [ModuleId] INT, 
    [ModuleName] VARCHAR(50) NOT NULL, 
    [ParentId] INT NULL
) 

INSERT INTO #Modules 
SELECT 1, 'Master', NULL 
UNION ALL SELECT 2, 'UsersGroup2', 1 
UNION ALL SELECT 3, 'UsersGroup3', 2 
UNION ALL SELECT 4, 'UsersGroup4', 3 
UNION ALL SELECT 5, 'UsersGroup5', 4 
UNION ALL SELECT 6, 'UsersGroup6', 5 
UNION ALL SELECT 7, 'UsersGroup7', 5 
UNION ALL SELECT 8, 'UsersGroup8', NULL 
UNION ALL SELECT 9, 'UsersGroup9', NULL 
UNION ALL SELECT 10, 'UsersGroup10', 9 
UNION ALL SELECT 11, 'UsersGroup11', 10 
UNION ALL SELECT 12, 'UsersGroup12', 11 
UNION ALL SELECT 14, 'UsersGroup14', 12 
UNION ALL SELECT 15, 'UsersGroup15', 9 

;WITH CTE AS (
    SELECT 
        1 AS [Level], 
        [ModuleId], 
        [ModuleId] AS RootModuleId, -- 根节点的ID就是自身
        [ModuleName] AS [GrandParent], 
        [ModuleName], 
        [ParentId] 
    FROM #Modules 
    WHERE [ParentId] IS NULL 

    UNION ALL 

    SELECT 
        cycle.[Level] + 1, 
        base.[ModuleId], 
        cycle.RootModuleId, -- 把父级的根ID传递给当前节点
        cycle.[GrandParent], 
        base.[ModuleName], 
        base.[ParentId] 
    FROM #Modules base 
    INNER JOIN CTE cycle ON cycle.[ModuleId] = base.[ParentId]
) 

SELECT 
    CTE.ModuleId, 
    CTE.RootModuleId AS GrandParentId, -- 这里直接取根节点ID
    COALESCE(NULLIF([GrandParent],[ModuleName])+'-->'+[ModuleName],[ModuleName]) AS [ModuleName] 
FROM CTE 

DROP TABLE #Modules

执行后的正确结果 #

运行上面的代码后，GrandParentId就会准确显示每个节点的根父节点ID了：

ModuleId GrandParentId ModuleName
1        NULL          Master
8        NULL          UsersGroup8
9        NULL          UsersGroup9
10       9             UsersGroup9-->UsersGroup10
15       9             UsersGroup9-->UsersGroup15
11       9             UsersGroup9-->UsersGroup11
12       9             UsersGroup9-->UsersGroup12
14       9             UsersGroup9-->UsersGroup14
2        1             Master-->UsersGroup2
3        1             Master-->UsersGroup3
4        1             Master-->UsersGroup4
5        1             Master-->UsersGroup5
6        1             Master-->UsersGroup6
7        1             Master-->UsersGroup7

关键改动点 #

• 初始CTE部分：给根节点（ParentId IS NULL）的RootModuleId赋值为自身的ModuleId，因为它们自己就是根。
• 递归部分：子节点直接继承父级CTE的RootModuleId，这样不管层级多深，每个节点都能拿到最顶层的根ID。
• 最终查询：用RootModuleId作为GrandParentId输出，替换原来错误的ParentId。

这样就完美解决你获取根父节点ID的问题啦！

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