PostgreSQL中递归聚合子节点的所有父节点

要实现每个子节点聚合其所有祖先节点（包括直接父和间接父），我们可以用递归CTE向上遍历层级结构，最后通过ARRAY_AGG聚合数组。针对你的表结构和需求，具体实现如下：

完整查询代码 #

WITH RECURSIVE parent_hierarchy AS (
    -- 初始步骤：获取每个节点的直接父节点
    SELECT child, parent
    FROM rel
    UNION ALL
    -- 递归步骤：向上遍历，获取当前父节点的父节点，直到父节点为NULL时停止
    SELECT ph.child, r.parent
    FROM parent_hierarchy ph
    JOIN rel r ON ph.parent = r.child
    WHERE ph.parent IS NOT NULL
)
SELECT 
    child,
    -- 根节点（parent为NULL的节点）单独返回{NULL}，其他节点聚合所有非空父节点
    CASE
        WHEN EXISTS (SELECT 1 FROM rel WHERE child = ph.child AND parent IS NULL)
        THEN ARRAY[NULL]
        ELSE ARRAY_AGG(parent) -- 顺序无关，若需要按层级从近到远排序可加ORDER BY，比如ORDER BY (SELECT COUNT(*) FROM parent_hierarchy ph2 WHERE ph2.child = ph.child AND ph2.parent = ph.parent)
    END AS parents_array
FROM parent_hierarchy ph
GROUP BY child
ORDER BY child;

代码说明 #

1. 递归CTE部分：

   • 初始查询SELECT child, parent FROM rel获取每个节点的直接父节点；
   • 递归查询通过JOIN关联当前节点的父节点，继续向上查找祖先，WHERE ph.parent IS NOT NULL确保当父节点为NULL时停止递归，避免无效遍历。

2. 聚合部分：

   • 用CASE区分根节点（parent为NULL的节点），单独返回ARRAY[NULL]匹配你的示例；
   • 非根节点通过ARRAY_AGG将所有遍历到的父节点聚合为数组，你提到顺序无关，所以可以去掉ORDER BY，如果需要按“直接父→祖父→曾祖父”的顺序排列，可添加注释里的排序逻辑。

执行结果 #

运行上述查询后，会得到你期望的结果：

child | parents_array
-------+---------------
     1 | {NULL}
     2 | {1}
     3 | {1}
     4 | {3,1}
     5 | {2,1}
     6 | {4,3,1}
     7 | {2,1}
     8 | {7,2,1}
     9 | {8,7,2,1}

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