我有一个表表示父子关系的表。
我使用以下查询创建了一个示例表：
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基于您的示例： sqlfiddle/#!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 rel212N.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: sqlfiddle/#!11/59319/19
(I replaced the spaces for indention with underscores as SQLFiddle doesn't display the spaces correctly)
更多推荐
Postgresql查询获取存储在单个表中的n级父子关系
发布评论