PostgreSQL中如何获取层级表每行记录对应的根父ID

处理这类嵌套深度不固定的树形结构关联，直接用PostgreSQL原生的**递归公用表表达式（Recursive CTE）**即可，不需要提前知道层级深度，也不需要写固定次数的自连接，执行效率高。 #

核心逻辑 #

递归CTE的执行分为两个固定阶段，数据库会自动循环遍历直到覆盖所有层级节点：

• 锚点阶段：先筛选出所有顶层根节点（即Parent ID为NULL的记录），这类节点的Root Parent ID按需求置空
• 递归阶段：每一轮用已经查出的节点去匹配原表中对应子节点（子节点的Parent ID等于已查出节点的ID），子节点直接继承所属链路的根节点ID，不需要重复向上追溯

实现代码 #

假设你的层级表名为hierarchy_table，存储ID和父ID的字段分别为id、parent_id。

直接查询得到结果 #

执行以下SQL可以直接输出带Root Parent ID的结果集，完全匹配给出的预期效果：

WITH RECURSIVE root_mapping AS (
    -- 锚点查询：取所有根节点
    SELECT
        id,
        parent_id,
        CAST(NULL AS TEXT) AS root_parent_id
    FROM hierarchy_table
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归查询：逐层关联子节点，继承根ID
    SELECT
        child.id,
        child.parent_id,
        CASE
            WHEN parent.root_parent_id IS NULL THEN parent.id
            ELSE parent.root_parent_id
        END AS root_parent_id
    FROM hierarchy_table child
    JOIN root_mapping parent
        ON child.parent_id = parent.id
    -- 可选过滤：排除id=parent_id的脏数据避免循环
    WHERE child.id != child.parent_id
)
SELECT * FROM root_mapping ORDER BY id;

持久化根父ID到原表 #

如果需要给原表新增字段长期存储Root Parent ID，按以下步骤执行：

1. 先给表加字段

ALTER TABLE hierarchy_table ADD COLUMN root_parent_id TEXT;

2. 用递归结果更新字段值

WITH RECURSIVE root_mapping AS (
    SELECT
        id,
        parent_id,
        CAST(NULL AS TEXT) AS root_parent_id
    FROM hierarchy_table
    WHERE parent_id IS NULL
    UNION ALL
    SELECT
        child.id,
        child.parent_id,
        CASE
            WHEN parent.root_parent_id IS NULL THEN parent.id
            ELSE parent.root_parent_id
        END AS root_parent_id
    FROM hierarchy_table child
    JOIN root_mapping parent ON child.parent_id = parent.id
    WHERE child.id != child.parent_id
)
UPDATE hierarchy_table t
SET root_parent_id = rm.root_parent_id
FROM root_mapping rm
WHERE t.id = rm.id;

优化注意事项 #

• 数据量较大时，给parent_id字段加B树索引，可以大幅提升递归关联的查询速度
• PostgreSQL默认递归深度上限为1000层，如果表中存在循环引用的脏数据（比如A的父是B，B的父是A），SQL会触发深度限制报错终止，不会无限执行
• 如果后续层级有变动，重新执行上述更新语句即可刷新所有节点的根父ID

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