sql - 如何在 PostgreSQL 中选择具有级别的表的所有层次结构

Question

我现在有一个问题。我有一个名为 places 的表，其结构如下:

• 编号
• parent_id
• 姓名

我想做一个选择来拥有这个表的所有层次结构。有一个数据的小例子:

(1, null, '123 Barclay St')
(2, 1, 'Floor 1')
(3, 1, 'Floor 2')
(4, 1, 'Floor 3')
(5, 2, 'Hall 1')
(6, 2, 'Room 1')
(7, 2, 'Room 2')
(8, 3, 'Room 3')
(9, null, '10 Thames St')

显然表格中的顺序不是这个。

所以我想用我的 SELECT 得到这个结果(有 9 行):

123 Barclay St
   Floor 1
      Hall 1
      Room 1
      Room 2
   Floor 2
      Room 3
   Floor 3
10 Thames St

而不是这个结果(我已经知道如何得到):

10 Thames St
123 Barclay St
   Floor 1
   Floor 2
   Floor 3
      Hall 1
      Room 1
      Room 2
      Room 3

如果你能帮助我，我先谢谢你。

Answer 1

这是一个使用递归 CTE 的解决方案:

WITH RECURSIVE cte AS (
    SELECT LPAD(id::text, 3, '0') AS marker, '   ' AS buffer,
        id, parent_id, name::text
    FROM yourTable t WHERE parent_id IS NULL
    FROM yourTable t WHERE parent_id IS NULL
    UNION ALL
        SELECT t2.marker || ':' || LPAD(t1.parent_id::text, 3, '0') || ':' ||
            LPAD(t1.id::text, 3, '0') AS marker,
            t2.buffer || '   ', t1.id, t1.parent_id, t2.buffer || t1.name
    FROM yourTable t1
    INNER JOIN cte t2
        ON t1.parent_id = t2.id
)

SELECT name FROM cte ORDER BY marker;

Demo

这里的基本思想是构建路径字符串，跟踪从每个节点返回其根的完整路径(根由 parent_id 为 NULL 的节点提供) >).然后，我们只需在此路径上执行单个 ORDER BY 即可生成您想要的订单。