Postgres递归CTE查询自关联表中指定节点的根节点ID

假设你的层级表结构如下（可根据实际字段名调整）：

• id: 节点唯一ID
• name: 节点名称
• parent_id: 父节点ID（根节点的parent_id为NULL或0）

实现SQL #

WITH RECURSIVE location_hierarchy AS (
    -- 锚点成员：定位目标节点（这里是"布鲁克林"）
    SELECT id, name, parent_id
    FROM locations
    WHERE name = '布鲁克林'
    
    UNION ALL
    
    -- 递归成员：向上遍历父节点
    SELECT l.id, l.name, l.parent_id
    FROM locations l
    JOIN location_hierarchy h ON l.id = h.parent_id
)
-- 筛选根节点并返回其ID
SELECT id AS root_id
FROM location_hierarchy
WHERE parent_id IS NULL;

关键说明 #

• 过滤目标节点：在锚点成员的WHERE子句中直接指定name = '布鲁克林'，就能精准定位起始节点。
• 递归遍历逻辑：通过自连接不断向上追溯父节点，直到遍历到没有上层节点的根节点。
• 仅返回根节点：最后通过parent_id IS NULL（如果你的根节点用0标识，就改成parent_id = 0）筛选出根节点，只取它的ID即可。

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