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

CREATE TABLE test

(

id text,

pid text,

msg text

)

insert into test(id,pid,msg) values(&＃39;1&＃39;,&＃39;&＃39;,&＃39;一级(1)&＃39;);

insert into test(id,pid,msg) values(&＃39;2&＃39;,&＃39;&＃39;,&＃39;一级(2)&＃39;);

insert into test(id,pid,msg) values(&＃39;3&＃39;,&＃39;&＃39;,&＃39;一级(3)&＃39;);

insert into test(id,pid,msg) values(&＃39;4&＃39;,&＃39;&＃39;,&＃39;一级(4)&＃39;);

insert into test(id,pid,msg) values(&＃39;11&＃39;,&＃39;1&＃39;,&＃39;二级(1)&＃39;);

insert into test(id,pid,msg) values(&＃39;22&＃39;,&＃39;2&＃39;,&＃39;二级(2)&＃39;);

insert into test(id,pid,msg) values(&＃39;33&＃39;,&＃39;3&＃39;,&＃39;二级(3)&＃39;);

insert into test(id,pid,msg) values(&＃39;44&＃39;,&＃39;4&＃39;,&＃39;二级(4)&＃39;);

insert into test(id,pid,msg) values(&＃39;111&＃39;,&＃39;11&＃39;,&＃39;三级(1)&＃39;);

insert into test(id,pid,msg) values(&＃39;222&＃39;,&＃39;22&＃39;,&＃39;三级(2)&＃39;);

insert into test(id,pid,msg) values(&＃39;333&＃39;,&＃39;33&＃39;,&＃39;三级(3)&＃39;);

insert into test(id,pid,msg) values(&＃39;444&＃39;,&＃39;44&＃39;,&＃39;三级(4)&＃39;);

2、查询

with recursive tmp as

(

select a.id,a.pid,a.msg from test a where id=&＃39;1&＃39;

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

分类:数据库

评论