postgresql中的树遍历查询 博客分类: sql技巧 postgresql
程序员文章站
2024-03-19 19:39:10
...
废话不多说直接coding
1、建表
2、查询
结果:
"一级(1)"
"二级(1)"
"三级(1)"
1、建表
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)');
2、查询
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
结果:
"一级(1)"
"二级(1)"
"三级(1)"