Posted by 一、示例数据
- CREATE TABLE test
- (
- id text,
- pid text,
- msg text
- )
- insert into test(id,pid,msg) values(‘1’,”,’一级（1）’);
- insert into test(id,pid,msg) values(‘2’,”,’一级（2）’);
- insert into test(id,pid,msg) values(‘3’,”,’一级（3）’);
- insert into test(id,pid,msg) values(‘4’,”,’一级（4）’);
- insert into test(id,pid,msg) values(’11’,’1′,’二级（1）’);
- insert into test(id,pid,msg) values(’22’,’2′,’二级（2）’);
- insert into test(id,pid,msg) values(’33’,’3′,’二级（3）’);
- insert into test(id,pid,msg) values(’44’,’4′,’二级（4）’);
- insert into test(id,pid,msg) values(‘111′,’11’,’三级（1）’);
- insert into test(id,pid,msg) values(‘222′,’22’,’三级（2）’);
- insert into test(id,pid,msg) values(‘333′,’33’,’三级（3）’);
- insert into test(id,pid,msg) values(‘444′,’44’,’三级（4）’);
二、查询
- with recursive tmp as
- (
- select a.id,a.pid,a.msg from test a where id=’1′
- union all
- select a.id,a.pid,a.msg from test a inner join tmp t on t.id=a.pid
- )
- select msg from tmp
本文链接地址: postgresql 递归树查询