postgresql 遍历参数_postgresql中的树遍历查询

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)"

分享到：

2018-07-31 15:49

浏览 2053

分类:数据库

评论