Oracle查询：如何获取子节点的直接父节点与根父节点

要同时拿到子节点的直接父节点和根父节点，最通用的方案是用递归CTE（公共表表达式），适用于MySQL 8.0+、PostgreSQL、SQL Server等支持递归语法的数据库。以下是具体实现：

递归CTE实现方案 #

递归CTE可以逐层向上遍历节点的父级，直到找到最顶层的根节点（通常根节点的main_Line_id为NULL），最终一次性取出直接父和根父信息：

WITH RECURSIVE UserTypeHierarchy AS (
    -- 初始查询：获取每个非根节点的直接父节点
    SELECT 
        Id AS child_id,
        main_Line_id AS direct_parent_id,
        main_Line_id AS current_parent_id
    FROM tablea
    WHERE main_Line_id IS NOT NULL
    UNION ALL
    -- 递归遍历：向上查找父节点的父节点，直到根节点
    SELECT 
        uh.child_id,
        uh.direct_parent_id,
        t.main_Line_id AS current_parent_id
    FROM UserTypeHierarchy uh
    JOIN tablea t ON uh.current_parent_id = t.Id
    WHERE t.main_Line_id IS NOT NULL
)
-- 最终结果：提取每个子节点的直接父和根父
SELECT 
    child_id AS child,
    direct_parent_id AS parent,
    -- 根节点是遍历到最顶层的节点
    (SELECT Id FROM tablea WHERE (uh.current_parent_id IS NULL AND Id = uh.direct_parent_id) OR Id = uh.current_parent_id) AS root_parent
FROM UserTypeHierarchy uh
WHERE uh.current_parent_id IS NULL
-- 补充根节点自身的记录（如果需要）
UNION ALL
SELECT 
    Id AS child,
    NULL AS parent,
    Id AS root_parent
FROM tablea
WHERE main_Line_id IS NULL;

逻辑说明 #

1. 初始查询先筛选出所有非根节点，记录它们的ID、直接父ID，以及当前遍历的父节点ID（初始就是直接父）。
2. 递归部分不断将当前父节点作为子节点，向上查找它的父节点，直到父节点的main_Line_id为NULL（即根节点）。
3. 最终查询只保留遍历到根节点的记录，同时补充根节点自身的信息（如果业务需要）。

兼容低版本数据库的方案（固定层级） #

如果你的数据库不支持递归CTE（比如MySQL 5.x），且节点层级固定（比如最多3层），可以用多层自连接实现：

SELECT 
    c.Id AS child,
    p.Id AS parent,
    -- 根节点判断：如果直接父是根节点，就取直接父；否则取直接父的父节点
    CASE 
        WHEN p.main_Line_id IS NULL THEN p.Id
        ELSE r.Id
    END AS root_parent
FROM tablea c
LEFT JOIN tablea p ON c.main_Line_id = p.Id
LEFT JOIN tablea r ON p.main_Line_id = r.Id
-- 处理根节点自身
UNION ALL
SELECT 
    Id AS child,
    NULL AS parent,
    Id AS root_parent
FROM tablea
WHERE main_Line_id IS NULL;

这种方式的局限性是只能处理固定层级的结构，层级变化时需要修改SQL。

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