如何从SQL表存储的层级数据中查找各节点对应的根节点

查询目标：遍历树形层级数据，为每个节点匹配其所属路径最顶层的根节点值。
之前使用Recursive CTE返回结果异常，通常是递归逻辑中没有透传顶层根节点值，中途将路径上的中间父节点识别为根节点导致的。

基础信息 #

涉及表字段：

• Node：当前节点唯一标识
• Parent_Node：当前节点的直接父节点标识
  样例数据：

正确递归CTE写法 #

核心逻辑分为两部分：

1. 锚点查询先筛选所有根节点（Parent_Node IS NULL的节点），这类节点自身的根节点值为NULL
2. 递归关联逐层向下查找子节点时，直接继承父节点已经匹配到的顶层根节点值，不做中途替换

WITH RECURSIVE hierarchy_cte AS (
    -- 锚点：定位所有根节点
    SELECT
        Node,
        Parent_Node,
        NULL AS Root_Node
    FROM your_table
    WHERE Parent_Node IS NULL

    UNION ALL

    -- 递归：遍历子节点，透传顶层根节点
    SELECT
        t.Node,
        t.Parent_Node,
        CASE
            WHEN c.Parent_Node IS NULL THEN c.Node
            ELSE c.Root_Node
        END AS Root_Node
    FROM your_table t
    INNER JOIN hierarchy_cte c ON t.Parent_Node = c.Node
)
SELECT
    Node,
    Root_Node AS `Root Node`
FROM hierarchy_cte
ORDER BY Node;

注意：使用时将代码中的your_table替换为实际业务表名即可。

返回结果 #

执行上述语句后将得到符合预期的输出：

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