SQL 用于获取存储在单个表中的 n 级父子关系的 Postgresql 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14659856/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql query for getting n-level parent-child relation stored in a single table
提问by saji89
I have a table denoting parent-child relations. The relations can go n-level deep.
我有一张表表示亲子关系。关系可以深入 n 级。
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
);
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.
我需要查询表并分层显示父子关系。我仍然不知道如何使用 sql 查询查询 n 级深度。
For the sqlfiddle eg, the expected hierarchy of output:
对于 sqlfiddle 例如，预期的输出层次结构：
rel1
rel11
rel111
rel112
rel1121
rel2
rel21
rel211
rel212
N.B:The value n, in n-levelis unknown.
注意：值 n, inn-level是未知的。
DB Design:
数据库设计：
Is there any better way such a relation can be expressed in the database for easy querying.?
有没有更好的方法可以在数据库中表达这种关系以便于查询。？
回答by a_horse_with_no_name
With Postgres you can use a recursive common table expression:
使用 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 based on your example: http://sqlfiddle.com/#!11/59319/19
SQLFiddle 基于您的示例：http://sqlfiddle.com/#!11/59319/19
(I replaced the spaces for indention with underscores as SQLFiddle doesn't display the spaces correctly)
（我用下划线替换了缩进空格，因为 SQLFiddle 没有正确显示空格）
