SQL查询：从父子节点单表递归获取未知深度的全层级数据

• 单表存储多棵深度不定的树形层级链路，表字段为ID、Parent、Child、Level、Description
• 表内示例数据：1条4级链路（根节点Child=10，逐级关联到末级Child=40）；1条2级链路（根节点Child=11，关联子节点Child=21）
• 已知前置查询可拿到单条链路的末级节点单行数据，示例：ID=1、Field1=123、Field2=456，对应层级表属性Parent=30、Child=40、Level=4、Description=Level 4；其中Field1、Field2仅末级节点存有效值
• 需求：不提前预知链路总层级的前提下，查询得到该链路全层级数据；所有结果行复用末级节点的ID、Field1、Field2值，关联对应层级的Level、Description，最终按Level1到LevelN的顺序返回全量记录，示例4级链路需返回4行对应数据。

用递归公用表表达式（Recursive CTE）实现任意深度链路的自动回溯，主流数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle 11gR2+）均支持该语法。
核心逻辑：

1. 锚点段：以已知末级节点作为递归起点，固定携带末级的ID、Field1、Field2值
2. 递归段：逐行用当前节点的Parent值匹配上层节点的Child值，向上回溯直到找不到父节点为止
3. 最终结果按Level升序排序输出即可。

参考SQL #

如果已经提前拿到末级节点的参数值，直接用以下写法，将表名、参数值替换为实际值即可：

WITH RECURSIVE hierarchy_trace AS (
    -- 锚点：传入已知末级节点信息
    SELECT 
        1 AS ID,
        123 AS Field1,
        456 AS Field2,
        Parent,
        Child,
        Level,
        Description
    FROM hierarchy_table
    WHERE Child = 40 AND Level = 4

    UNION ALL

    -- 递归向上查找所有父级节点
    SELECT 
        ht.ID,
        ht.Field1,
        ht.Field2,
        t.Parent,
        t.Child,
        t.Level,
        t.Description
    FROM hierarchy_trace ht
    JOIN hierarchy_table t ON ht.Parent = t.Child
)
-- 按层级升序返回结果
SELECT ID, Field1, Field2, Level, Description
FROM hierarchy_trace
ORDER BY Level ASC;

如果需要直接对接现有的末级节点查询逻辑，不需要手动传参，用嵌套CTE写法即可：

WITH RECURSIVE last_node AS (
    -- 此处替换为原有查询末级节点的SQL，需返回ID、Field1、Field2、Parent、Child、Level、Description字段
    SELECT ID, Field1, Field2, Parent, Child, Level, Description
    FROM hierarchy_table
    WHERE ID = 1 -- 替换为原有的末级过滤条件
),
hierarchy_trace AS (
    SELECT * FROM last_node
    UNION ALL
    SELECT 
        ln.ID,
        ln.Field1,
        ln.Field2,
        t.Parent,
        t.Child,
        t.Level,
        t.Description
    FROM hierarchy_trace ht
    CROSS JOIN last_node ln -- 固定关联末级节点的三个公共字段
    JOIN hierarchy_table t ON ht.Parent = t.Child
)
SELECT ID, Field1, Field2, Level, Description
FROM hierarchy_trace
ORDER BY Level ASC;

返回结果示例 #

针对题中给出的4级链路样例，上述SQL返回结果如下，完全匹配需求：

注意事项 #

• 代码中hierarchy_table需替换为实际使用的层级表名
• 该写法自动适配任意深度的层级链路，不需要提前写死关联层数，2级、4级甚至更深的链路都能正常返回全量数据
• 如果使用的是不支持Recursive CTE的老旧数据库版本（比如MySQL 5.x），需要改成自定义函数或存储过程实现循环回溯，逻辑和递归CTE一致。

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