公用表表达式内的反向聚合 [英] Reverse aggregation inside of common table expression
本文介绍了公用表表达式内的反向聚合的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我希望下面的查询返回所有带有各自子女的人.
I would have expected that the following query returns all people with their respective children.
WITH RECURSIVE nested_people (id, name, children) AS (
SELECT id, name, NULL::JSON AS children
FROM people
WHERE parent_id IS NULL
UNION ALL
SELECT people.id, people.name, ROW_TO_JSON(nested_people.*) AS children
FROM people
JOIN nested_people ON people.parent_id = nested_people.id
)
SELECT * FROM nested_people;
但实际上,它的作用恰恰相反.我想不出无需额外的CTE就能进行正确嵌套的方法.有办法吗?
But actually it does the exact reverse. I can't think of a way to do correct nesting without having to an additional CTE. Is there a way?
示例数据
+----+-------+-----------+
| id | name | parent_id |
+----+-------+-----------+
| 1 | Adam | null |
| 2 | Abel | 1 |
| 3 | Cain | 1 |
| 4 | Enoch | 3 |
+----+-------+-----------+
结果
+----+-------+--------------------------------------------------------------------------+
| id | name | children |
+----+-------+--------------------------------------------------------------------------+
| 1 | Adam | null |
| 2 | Abel | {"id":1,"name":"Adam","children":null} |
| 3 | Cain | {"id":1,"name":"Adam","children":null} |
| 4 | Enoch | {"id":3,"name":"Cain","children":{"id":1,"name":"Adam","children":null}} |
+----+-------+--------------------------------------------------------------------------+
预期结果
+----+-------+----------------------------------------------------------------------------------------------------------------------+
| id | name | children |
+----+-------+----------------------------------------------------------------------------------------------------------------------+
| 1 | Adam | [{"id":2, "name":"Abel", "children":null},{"id":3,"name":"Cain","children":[{"id":4,"name":"Enoch","children":null}] |
| 2 | Abel | null |
| 3 | Cain | [{"id":4,"name":"Enoch","children":null}] |
| 4 | Enoch | null |
+----+-------+----------------------------------------------------------------------------------------------------------------------+
推荐答案
此rCTE从另一侧遍历树:
This rCTE traverses the tree from the other side:
WITH RECURSIVE cte AS (
SELECT id, parent_id, name, NULL::JSON AS children
FROM people p
WHERE NOT EXISTS ( -- only leaf nodes; see link below
SELECT 1 FROM people
WHERE parent_id = p.id
)
UNION ALL
SELECT p.id, p.parent_id, p.name, row_to_json(c) AS children
FROM cte c
JOIN people p ON p.id = c.parent_id
)
SELECT id, name, json_agg(children) AS children
FROM cte
GROUP BY 1, 2;
使用 json_agg()
聚合外部SELECT
中每个节点的多个分支.
与您期望的结果略有不同:
Use json_agg()
to aggregate multiple branches per node in the outer SELECT
.
Minor differences to your desired result:
- 这包括
children
列中的parent_id
. - 单个节点未包装到数组中.
- This includes the
parent_id
in thechildren
column. - A single node is not wrapped into an array.
这两种方法都可以适应,但我希望结果对您来说还是可以的.
Either can be adapted, but I'd expect the result is OK for you as is.
如何识别叶节点:
这篇关于公用表表达式内的反向聚合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文