Postgres递归查询：获取指定子节点的所有根父节点

嘿，这个需求我太熟悉了！要从指定的子节点集合里找出各自对应的顶层根节点（也就是没有父节点的节点），PostgreSQL里用**递归CTE（公用表表达式）**是最直接高效的方案，我给你写好示例代码并解释清楚：

假设你的数据表名为posts（你可以改成自己的表名），要查询的子节点ID集合是ARRAY[5, 12, 27]，对应的SQL语句如下：

WITH RECURSIVE node_ancestors AS (
    -- 第一步：先把传入的目标子节点选出来作为递归起点
    SELECT id, parent_id
    FROM posts
    WHERE id = ANY(ARRAY[5, 12, 27]) -- 替换成你的子节点ID集合
    UNION ALL
    -- 第二步：递归向上遍历父节点链，直到碰到根节点为止
    SELECT t.id, t.parent_id
    FROM posts t
    JOIN node_ancestors na ON t.id = na.parent_id
    WHERE t.parent_id IS NOT NULL -- 还没到根节点就继续往上找
)
-- 最后筛选出所有根节点，用DISTINCT避免同一个根被重复返回
SELECT DISTINCT id AS root_node_id
FROM node_ancestors
WHERE parent_id IS NULL;

• 递归CTE的工作逻辑：node_ancestors会先获取你指定的子节点，然后不断向上关联它们的父节点、父节点的父节点……直到遍历到没有父节点（parent_id IS NULL）的节点为止。
• 去重处理：用DISTINCT是因为不同的子节点可能属于同一个根节点，避免结果里出现重复的根ID。
• 适配特殊场景：如果你的表不是用NULL表示根节点，而是用0或者其他固定值，只需要把代码里的parent_id IS NULL改成对应的判断（比如parent_id = 0）就行。
• 性能优化：如果表的数据量很大，记得给id和parent_id字段建立索引，这样递归查询的速度会快很多。

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