我需要一个递归查询的帮助.假设下表：
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,'Peter'),(2,'Thomas'),(5,2,'David'),(1,'Rob'),(8,'Brian');
我可以通过以下查询检索所有人和他们的孩子的列表：
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;
我如何按顺序列出它们,并按名称对第一级项目排序？例如,期望的输出将是：
id,name 8,"Brian" 3,"Peter" 1,0; "Rob" 2,"Thomas" 5," David"
谢谢,
**编辑.请注意,添加ORDER BY将不起作用：**
WITH RECURSIVE recursetree(id,path,name) AS (
SELECT
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子句(即：名称 – 像有些已经建议)将导致结果失去它的父子关系.
另请参阅这篇关于CTE在Postgresql：
wiki.phpfreakz.nl中的文章
原文链接：https://www.f2er.com/postgresql/192505.html编辑：尝试使用数组：
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;
