PostgreSQL-选择具有级别的表的所有层次结构 [英] PostgreSQL - Select all the hierarchy of a table with levels

问题描述

此刻我有问题。
我有一个名为places的表，其结构如下：

I have a problem in this moment. I have a table called places with this structure:

• id


• parent_id


• 名称

我想进行选择以具有此表的所有层次结构。有一个数据的小例子：

I want to do a selection to have all the hierarchy of this table. There's a little example of data:

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

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

Obviously the order in the table is not this one.

所以我想通过SELECT（9行）获得此结果：

So I want to get this result with my SELECT (with the 9 rows):

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

不是这个结果（我已经知道如何获得） ：

And not this result (that I already know how to get) :

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

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

If you can help me, I thank you in advance.

推荐答案

这是使用递归CTE的解决方案：

Here is a solution using recursive CTEs:

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;

演示

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

The basic idea here is to build path strings which track the complete path from every node going to back its root (the root being given by a node whose parent_id is NULL). Then, we simply do a single ORDER BY on this path to generate the order you want.

这篇关于PostgreSQL-选择具有级别的表的所有层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！