如何用SQL获取子查询返回节点的所有父层级及自身节点？

当然可以搞定这种层级结构的查询！递归公共表表达式（CTE）是处理树形数据的绝佳工具，正好能满足你“获取子查询节点及其所有上层父节点”的需求。

先明确你的需求：给定表H的父子结构，子查询返回A2a、A2b、B3，要返回这些节点本身，以及它们所有的父节点（直到最顶层）——也就是最终结果A、A2、A2a、A2b、B、B3。

具体SQL实现 #

下面是针对你场景的完整查询，用递归CTE来遍历父节点链：

WITH RECURSIVE NodeHierarchy AS (
    -- 第一步：锚点查询，获取子查询返回的目标节点
    SELECT Child AS Node
    FROM H
    WHERE Child IN ('A2a', 'A2b', 'B3') -- 替换成你的实际子查询，比如(SELECT ...)
    UNION ALL
    -- 第二步：递归向上查找父节点
    SELECT h.Parent
    FROM NodeHierarchy nh
    JOIN H h ON nh.Node = h.Child
    WHERE h.Parent IS NOT NULL -- 终止条件：当没有父节点时停止递归
)
-- 去重后输出所有符合条件的节点
SELECT DISTINCT Node
FROM NodeHierarchy
ORDER BY Node;

代码解释 #

• 锚点部分：先把子查询返回的节点作为递归的起点，把这些节点加入结果集。
• 递归部分：每次将当前节点的父节点加入结果集，循环这个过程直到遇到没有父节点的顶层节点。
• 去重与排序：用DISTINCT避免重复（比如A2会被A2a和A2b两次递归到），ORDER BY让结果更易读。

如果你的子查询是动态生成的（不是固定的几个值），直接把IN ('A2a', 'A2b', 'B3')替换成你的子查询即可，比如：

WHERE Child IN (SELECT target_child FROM your_subquery_table WHERE ...)

运行这段查询后，就能得到你想要的A、A2、A2a、A2b、B、B3啦！

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