欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

postgresql中的树遍历查询 博客分类: sql技巧 postgresql 

程序员文章站 2024-03-19 19:39:10
...
废话不多说直接coding
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)"


相关标签: postgresql