PostgreSQL 递归
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3307480/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL recursive with
提问by robdog
I need help with a recursive query. Assuming the following table:
我需要有关递归查询的帮助。假设下表：
CREATE TEMPORARY TABLE tree (
id integer PRIMARY KEY,
parent_id integer NOT NULL,
name varchar(50)
);
INSERT INTO tree (id, parent_id, name) VALUES (3, 0, 'Peter'), (2,0, 'Thomas'), (5,2, 'David'), (1, 0, 'Rob'), (8, 0, 'Brian');
I can retrieve a list of all people and their children with the following query:
我可以使用以下查询检索所有人及其孩子的列表：
WITH RECURSIVE recursetree(id, parent_id) AS (
SELECT id, parent_id FROM tree WHERE parent_id = 0
UNION
SELECT t.id, t.parent_id
FROM tree t
JOIN recursetree rt ON rt.id = t.parent_id
)
SELECT * FROM recursetree;
How can I list them in order, and also sort the first level items by name? For example, the desired output would be:
如何按顺序列出它们，并按名称对第一级项目进行排序？例如，所需的输出是：
id, parent_id, name
8, 0, "Brian"
3, 0, "Peter"
1, 0; "Rob"
2, 0, "Thomas"
5, 2, " David"
Thanks,
谢谢，
**EDIT. Please note that adding an ORDER BY won't work: **
**编辑。请注意，添加 ORDER BY 不起作用：**
WITH RECURSIVE recursetree(id, parent_id, path, name) AS (
SELECT
id,
parent_id,
array[id] AS path,
name
FROM tree WHERE parent_id = 0
UNION ALL
SELECT t.id, t.parent_id, rt.path || t.id, t.name
FROM tree t
JOIN recursetree rt ON rt.id = t.parent_id
)
SELECT * FROM recursetree ORDER BY path;
The above will retain the parent child relationship (children follow their parents), but applying any other ORDER BY clause (ie: name - like some have suggested) will cause the result to lose it's parent-child relationships.
以上将保留父子关系（孩子跟随他们的父母），但应用任何其他 ORDER BY 子句（即：名称 - 就像某些人建议的那样）将导致结果失去它的父子关系。
回答by Frank Heikens
See also this (translated) article about CTE's in PostgreSQL: wiki.phpfreakz.nl
另请参阅有关 PostgreSQL 中 CTE 的这篇（已翻译）文章：wiki.phpfreakz.nl
Edit: Try this one, using an array:
编辑：试试这个，使用数组：
WITH RECURSIVE recursetree(id, parent_ids, firstname) AS (
SELECT id, NULL::int[] || parent_id, name FROM tree WHERE parent_id = 0
UNION ALL
SELECT
t.id,
rt.parent_ids || t.parent_id,
name
FROM tree t
JOIN recursetree rt ON rt.id = t.parent_id
)
SELECT * FROM recursetree ORDER BY parent_ids;
