PostgreSQL如何通过任意子/孙ID递归获取最顶层父ID？

核心查询语句 #

直接替换目标ID即可获取对应顶层父节点：

WITH RECURSIVE top_parent AS (
    -- 初始锚点：定位到目标节点
    SELECT id, parent_id
    FROM person_v
    WHERE id = 1024 -- 替换为你要查询的ID
    UNION ALL
    -- 递归遍历：向上查找父节点，直到无父节点为止
    SELECT p.id, p.parent_id
    FROM person_v p
    JOIN top_parent tp ON p.id = tp.parent_id
)
-- 提取顶层节点：如果自身是顶层则返回ID，否则返回最顶层父ID
SELECT COALESCE(parent_id, id) AS topmost_id
FROM top_parent
WHERE parent_id IS NULL;

递归CTE工作机制拆解 #

递归CTE分为两个关键部分：

1. 锚点成员：首先获取目标节点的id和parent_id，作为递归的起始点。
2. 递归成员：通过JOIN关联上一轮递归的结果，用当前节点的parent_id作为新的查询条件，向上查找父节点。这个过程会重复执行，直到找不到新的父节点（即parent_id为NULL）时停止。

最后通过筛选parent_id IS NULL的行，用COALESCE处理边界情况：如果目标节点本身就是顶层（parent_id为NULL），则直接返回其id；否则返回最顶层父节点的id。

封装成函数（方便复用） #

如果需要多次查询，可以封装成SQL函数：

CREATE OR REPLACE FUNCTION get_top_parent(target_id INT)
RETURNS INT AS $$
WITH RECURSIVE top_parent AS (
    SELECT id, parent_id
    FROM person_v
    WHERE id = target_id
    UNION ALL
    SELECT p.id, p.parent_id
    FROM person_v p
    JOIN top_parent tp ON p.id = tp.parent_id
)
SELECT COALESCE(parent_id, id)
FROM top_parent
WHERE parent_id IS NULL;
$$ LANGUAGE sql STABLE;

调用方式：

SELECT get_top_parent(900); -- 返回300
SELECT get_top_parent(512); -- 返回512

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