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

小编典典

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

sql

我有一张桌子表示父母与子女的关系。这种关系可以达到n级深层次。

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

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，inn-level是未知的。

数据库设计：

有没有更好的方法可以在数据库中表达这种关系以便于查询。

阅读 234

2021-03-17

共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无法正确显示空格）

2021-03-17