PostgreSQL递归with

Question

我需要帮助处理一个递归查询。假设有以下表：

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');

我可以通过以下查询检索所有人及其子女的列表：

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, parent_id, name    
8, 0, "Brian"
3, 0, "Peter"
1, 0; "Rob"
2, 0, "Thomas"
5, 2, "  David"

谢谢，

**编辑。请注意，添加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;

以上代码将保留父子关系（子元素跟随父元素），但是应用任何其他ORDER BY子句（例如：像一些人建议的那样按名称排序）将导致结果失去其父子关系。

- robdog

---

一个简单的ORDERBY不就行了吗？ - Marcel Gheorghita

一个 ORDER BY 语句不会将子项放置在其父项下面，只会按名称对所有内容进行排序。 - robdog

Answer 1

请参阅关于PostgreSQL中CTE的这篇文章（已翻译）：wiki.phpfreakz.nl 编辑：尝试使用数组：

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;

- Frank Heikens

---

在CTE中使用数组，子级数组的元素数量总是比父级多，使用升序排列即可。 - Frank Heikens

可能需要使用“ORDER BY parent_ids, firstname”来按名称对顶级项目进行排序（它们都将以{0}作为其parent_ids）。 - araqnid

Frank，我不确定你在暗示什么。你是在建议将路径存储在数组中吗？如果是这样，我仍然需要按该字段排序以保留关系 - 这意味着我将无法按名称排序。请参见我上面的编辑。 - robdog

为什么按姓名排序不可能呢？这边运行良好。请展示一个排序失败的案例。（另外，0 不是正确的 parent_id，它并不存在。当没有父类时，最好使用 NULL） - Frank Heikens

当然不会！为什么要这样做呢？你不按照父级排序，为什么数据库要按照你没有告诉它的方式排序呢？ORDER BY parent_ids, name; - Frank Heikens

Answer 2

您可以在查询中添加路径并在最后按其排序：

WITH RECURSIVE recursetree(id, parent_id,path) AS (
  SELECT id, parent_id,id||'' as path FROM tree WHERE parent_id = 0
UNION
  SELECT t.id, t.parent_id,concat(rt.path,'_',t.id)
  FROM tree t
  JOIN recursetree rt ON rt.id = t.parent_id
) 
SELECT * FROM recursetree
ORDER BY rt.path;

- Bastiaan Wakkie