在单个表中获取n级父子关系的Postgresql查询

Question

我有一个表示父子关系的表格，这些关系可以延伸到多个级别。

我使用以下查询创建了一个示例表格：

CREATE SEQUENCE relations_rel_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
CREATE TABLE relations(
    rel_id bigint DEFAULT nextval('relations_rel_id_seq'::regclass) NOT NULL PRIMARY KEY,
    rel_name text,
    rel_display text,
    rel_parent bigint
);

SQLFiddle

我需要查询表格并按层次结构显示父子关系。 我仍然不知道如何使用SQL查询查询n级深度。

对于此sqlfiddle示例，期望的输出层次结构：

rel1
    rel11
        rel111
        rel112
            rel1121
rel2
    rel21
        rel211
        rel212

注意：n-level中的值n是未知的。

数据库设计：

是否有更好的方式可以在数据库中表示这样的关系，以便于进行查询？

- saji89

---

你也许在寻找WITH RECURSIVE，这里有一个例子（链接：https://dev59.com/vW3Xa4cB1Zd3GeqPgZeK）。 - mu is too short

@muistooshort，感谢您的建议。我会研究一下。您有没有关于替代数据库设计的建议，可以使在这种情况下查询和检索关系更容易？ - saji89

@saji89 请阅读手册中的此页面，它描述了你所需的WITH RECURSIVE查询。 - Ihor Romanchenko

@saji89 顺便提一下你的RDBMS版本是很好的。在 PostgreSQL 8.4 以下版本中是不支持递归查询的。 - Ihor Romanchenko

我认为你使用的rel_parent结构是树形结构中最自然的设计；它很容易理解，并且在使用WITH RECURSIVE进行常规操作时也很方便。你也可以查看“嵌套集”和“材料化路径”。 - mu is too short

Answer 1

使用Postgres，您可以使用递归公共表达式：

with recursive rel_tree as (
   select rel_id, rel_name, rel_parent, 1 as level, array[rel_id] as path_info
   from relations 
   where rel_parent is null
   union all
   select c.rel_id, rpad(' ', p.level * 2) || c.rel_name, c.rel_parent, p.level + 1, p.path_info||c.rel_id
   from relations c
     join rel_tree p on c.rel_parent = p.rel_id
)
select rel_id, rel_name
from rel_tree
order by path_info;

基于您的示例的SQLFiddle：http://sqlfiddle.com/#!11/59319/19

(我将缩进空格替换为下划线，因为SQLFiddle无法正确显示空格)

- user330315

---

很棒的答案，正是OP所需要的！但是在rpad(..)部分有一个小错误，将其更改为rpad('', p.level * 2, ' ')。同样，在SQL Fiddle上空格被替换为'_'。 - tscho

刚刚查阅了文档：空格是默认的填充字符（可选的第三个参数），因此我必须在SQLFiddle上显式地使用“_”填充字符。 - tscho

+1，哇。Postgresql 真是让我惊喜不断。谢谢。 - saji89

有一会儿我不知道如何将普通的 with 表达式与 with recursive 结合使用。看起来你应该将 with 表达式放在 with recursive 内部。 - mkataja

@mkataja：不，你只需要添加它。with recursive first_cte (...), second_cte (...) select * from second_cte。recursive关键字仅在初始的with关键字中一次是必需的-无论哪个CTE是递归的。 - user330315

嘿，那很有道理。不知怎么的，我以为recursive关键字需要与实际的递归CTE一起使用。 - mkataja