PostgreSQL递归 [英] PostgreSQL recursive with
问题描述
我需要有关递归查询的帮助。假定如下表:
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"
谢谢
**编辑。请注意,添加ORDER BY无效:**
**EDIT. Please note that adding an ORDER BY won't work: **
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;
以上内容将保留亲子关系(孩子跟随其父母),但应用任何其他ORDER BY子句(例如:name-如某些人所建议)将导致结果失去其父子关系。
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.
推荐答案
另请参见这篇有关PostgreSQL中CTE的文章(翻译的): wiki.phpfreakz.nl
See also this (translated) article about CTE's in PostgreSQL: wiki.phpfreakz.nl
编辑:使用数组尝试以下操作:
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;
这篇关于PostgreSQL递归的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!