我有一张表示亲子关系的表.关系可以深入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
);
我需要查询表并分层显示父子关系.我仍然不了解如何使用SQL查询查询n级深度.
rel1
rel11
rel111
rel112
rel1121
rel2
rel21
rel211
rel212
N.B：n级中的值n是未知的.
数据库设计：
Is there any better way such a relation can be expressed in the
database for easy querying.?
使用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
