PostgreSQL如何递归聚合不同列的关联属性为数组？

你的递归查询存在两处问题：

• 递归分支的SELECT语句中，t.id_start和t.id_end之间缺少逗号，会导致语法错误
• 关联条件r.id_start = t.id_end不符合链式关联的逻辑，应该改为r.id_end = t.id_start，这样才能顺着关联链向下遍历

下面根据不同场景给出具体实现：

场景1：数据表只有一条关联链（比如你的示例数据） #

可以通过递归CTE遍历完整的关联链，同时逐步构建节点数组，最后取链的终点对应的数组即可：

WITH RECURSIVE q_rec AS (
    -- 先找到链的起点：没有被作为id_end的id_start
    SELECT id_start, id_end, ARRAY[id_start, id_end] AS node_array
    FROM my_table
    WHERE id_start NOT IN (SELECT id_end FROM my_table)
    UNION ALL
    -- 递归遍历，把下一个节点追加到数组里
    SELECT t.id_start, t.id_end, r.node_array || t.id_end
    FROM my_table t
    JOIN q_rec r ON r.id_end = t.id_start
)
-- 取链的终点对应的数组，就是完整的关联元素集合
SELECT node_array AS 关联元素数组
FROM q_rec
WHERE id_end NOT IN (SELECT id_start FROM my_table);

针对你的示例数据，这个查询会返回[1,2,3,4]。

场景2：数据表有多条独立的关联链 #

如果存在多条不相交的关联链，需要按链分组聚合：

WITH RECURSIVE q_rec AS (
    -- 初始步骤：标记每条链的根节点（起点），并初始化节点数组
    SELECT id_start, id_end, id_start AS root_node, ARRAY[id_start, id_end] AS node_array
    FROM my_table
    WHERE id_start NOT IN (SELECT id_end FROM my_table)
    UNION ALL
    -- 递归遍历，继承根节点并追加新节点
    SELECT t.id_start, t.id_end, r.root_node, r.node_array || t.id_end
    FROM my_table t
    JOIN q_rec r ON r.id_end = t.id_start
),
-- 收集所有节点，包括可能存在的孤立节点
all_nodes AS (
    SELECT unnest(node_array) AS node, root_node
    FROM q_rec
    UNION
    SELECT id_start AS node, id_start AS root_node
    FROM my_table
    WHERE id_start NOT IN (SELECT unnest(node_array) FROM q_rec)
)
-- 按根节点分组，聚合每条链的有序数组
SELECT root_node, ARRAY_AGG(node ORDER BY node) AS 关联元素数组
FROM all_nodes
GROUP BY root_node;

简化版：不区分链，直接聚合所有关联节点 #

如果不需要区分多条链，只想把所有关联的节点合并成一个数组，用这个更简单的写法：

WITH RECURSIVE q_rec AS (
    -- 从链的起点开始
    SELECT id_start AS node
    FROM my_table
    WHERE id_start NOT IN (SELECT id_end FROM my_table)
    UNION ALL
    -- 递归遍历所有关联节点
    SELECT t.id_end AS node
    FROM my_table t
    JOIN q_rec r ON r.node = t.id_start
)
-- 聚合为有序数组
SELECT ARRAY_AGG(node ORDER BY node) AS 关联元素数组
FROM q_rec;

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