SQL如何实现层级结构扁平化 查询每个节点的终极父节点

实现思路 #

用递归公用表表达式（CTE）实现层级遍历，是当前主流SQL数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle 11g+等）通用的实现方案，逻辑分为两部分：

• 锚点查询：先识别顶层父节点（没有出现在Child列中的Parent即为顶层节点），取其直接子节点记录，将当前Parent作为初始Ultimate Parent
• 递归查询：基于上一轮的查询结果，将上一轮的Child作为下一轮关联的Parent，关联原始层级表获取下一级子节点，始终继承顶层节点的Ultimate Parent值，直到没有下一级子节点为止

完整SQL代码 #

-- 假设原始表名为 hierarchy，字段为 Parent、Child
WITH RECURSIVE cte AS (
    -- 锚点：取顶层父节点的直接子节点
    SELECT 
        Parent,
        Child,
        Parent AS `Ultimate Parent`
    FROM hierarchy
    WHERE Parent NOT IN (SELECT Child FROM hierarchy)
    
    UNION ALL
    
    -- 递归遍历下一级子节点
    SELECT 
        h.Parent,
        h.Child,
        c.`Ultimate Parent`
    FROM hierarchy h
    INNER JOIN cte c ON h.Parent = c.Child
)
SELECT * FROM cte ORDER BY `Ultimate Parent`, Parent;

结果验证 #

代入你提供的样例数据执行后，输出结果和期望完全一致：

Parent | Child | Ultimate Parent
-------|------ |----------------
A      | B     | A
B      | C     | A
C      | D     | A
E      | F     | E
F      | G     | E

注意事项 #

• 如果使用的数据库不支持递归CTE（如MySQL 5.x版本），可以通过自定义存储函数实现层级向上遍历，核心逻辑是输入当前节点，循环向上查找父节点直到找不到上级后返回
• 针对存在循环关联的异常数据（如A→B、B→A），需要添加递归深度限制避免栈溢出，不同数据库的配置参数不同，可根据使用的数据库版本搜索对应配置方法

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