| id_start | id_end |
|---|---|
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
我想收集像[1, 2, 3, 4]这样"连接"的元素.(例如，在数组中)
我try 了递归查询，如下所示:
WITH RECURSIVE q_rec AS (
SELECT id_start,
id_end
FROM my_table
UNION
SELECT t.id_start
t.id_end
FROM my_table t
INNER JOIN q_rec r ON r.id_start = t.id_end
)
SELECT *
FROM q_rec;
但是，即使它们不在同一列中，我如何将它们聚合到一个数组中呢？
另一种方法将递归查询中的所有ID收集为一个表.然后应用聚合.
WITH RECURSIVE cte AS (
SELECT id_start AS first_id,
id_start AS id
FROM tab t1
WHERE NOT EXISTS(SELECT 1 FROM tab t2 WHERE t1.id_start = t2.id_end)
UNION ALL
SELECT cte.first_id,
tab.id_end
FROM cte
INNER JOIN tab
ON cte.id = tab.id_start
)
SELECT first_id,
ARRAY_AGG(id) AS ids
FROM cte
GROUP BY first_id
Output:
| first_id | ids |
|---|---|
| 1 | [1,2,3,4] |
查看演示here.