Postgresql查询获取存储在单个表中的n级父子关系 [英] Postgresql query for getting n-level parent-child relation stored in a single table

问题描述

我有一个表表示父子关系的表。

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

  CREATE SEQUENCE relation_rel_id_seq 
 INCREMENT BY 1 
 NO MAXVALUE 
 NO MINVALUE 
 CACHE 1; 
 CREATE TABLE关系（
 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 
  

注意：

p>

有没有更好的方式这种关系可以在
数据库中表示，以方便查询。

$ b $使用Postgres可以使用递归公共表表达式：

pre> 使用递归rel_tree作为（
select rel_id，rel_name，rel_parent，1 as level，array [rel_id] as path_info
from relation
其中rel_parent是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
从关系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不能正确显示空格）

I have a table denoting parent-child relations. The relations can go n-level deep.

I have created a sample table using the following query:

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

I need to query the table and display the parent-child relations hierarchically. I'm still not getting an idea regarding how to query n-level deep using sql query.

For the sqlfiddle eg, the expected hierarchy of output:

rel1
    rel11
        rel111
        rel112
            rel1121
rel2
    rel21
        rel211
        rel212

N.B: The value n, in n-level is unknown.

DB Design:

Is there any better way such a relation can be expressed in the database for easy querying.?

解决方案

With Postgres you can use a recursive common table expression:

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 based on your example: http://sqlfiddle.com/#!11/59319/19

(I replaced the spaces for indention with underscores as SQLFiddle doesn't display the spaces correctly)

这篇关于Postgresql查询获取存储在单个表中的n级父子关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！