转载:https://my.oschina.net/Kenyon/blog/55137
开发有需求,说需要对一张地区表进行递归查询,Postgres中有个with recursive的查询方式,可以满足递归查询(一般>=2层)。 测试如下:
create table tb(id varchar(3),pid varchar(3),name varchar(10));
insert into tb values('002',0,'浙江省');
insert into tb values('001','广东省');
insert into tb values('003','002','衢州市');
insert into tb values('004','杭州市') ;
insert into tb values('005','湖州市');
insert into tb values('006','嘉兴市') ;
insert into tb values('007','宁波市');
insert into tb values('008','绍兴市') ;
insert into tb values('009','台州市');
insert into tb values('010','温州市') ;
insert into tb values('011','丽水市');
insert into tb values('012','金华市') ;
insert into tb values('013','舟山市');
insert into tb values('014','004','上城区') ;
insert into tb values('015','下城区');
insert into tb values('016','拱墅区') ;
insert into tb values('017','余杭区') ;
insert into tb values('018','011','金东区') ;
insert into tb values('019','001','广州市') ;
insert into tb values('020','深圳市') ;
测试语句,查询浙江省及以下县市:
test=# with RECURSIVE cte as
test-# (
test(# select a.id,a.name,a.pid from tb a where id='002'
test(# union all
test(# select k.id,k.name,k.pid from tb k inner join cte c on c.id = k.pid
test(# )select id,name from cte;
id | name -----+-------- 002 | 浙江省
003 | 衢州市 004 | 杭州市
005 | 湖州市 006 | 嘉兴市
007 | 宁波市 008 | 绍兴市
009 | 台州市 010 | 温州市
011 | 丽水市 012 | 金华市
013 | 舟山市 014 | 上城区
015 | 下城区 016 | 拱墅区
017 | 余杭区 018 | 金东区
(17 rows)
如果查询有报错如死循环跳出,则需要检查下父字段与子字段的数据是否有相同。
如果想按层次分别显示出来,也可以这么写
test=# with RECURSIVE cte as
(
select a.id,cast(a.name as varchar(100)) from tb a where id='002'
union all
select k.id,cast(c.name||'>'||k.name as varchar(100)) as name from tb k inner join cte c on c.id = k.pid
)select id,name from cte ;
id | name
-----+----------------------
002 | 浙江省
003 | 浙江省>衢州市
004 | 浙江省>杭州市
005 | 浙江省>湖州市
006 | 浙江省>嘉兴市
007 | 浙江省>宁波市
008 | 浙江省>绍兴市
009 | 浙江省>台州市
010 | 浙江省>温州市
011 | 浙江省>丽水市
012 | 浙江省>金华市
013 | 浙江省>舟山市
014 | 浙江省>杭州市>上城区
015 | 浙江省>杭州市>下城区
016 | 浙江省>杭州市>拱墅区
017 | 浙江省>杭州市>余杭区
018 | 浙江省>丽水市>金东区
(17 rows)
PS: MysqL貌似还没出这么一种功能,
附带说一下sqlServer的的递归查询,语法类似,不过把recursive去掉就可以了,如:
with cte as
(
select a.id,a.pid from tb a where id='002'
union all
select k.id,k.pid from tb k inner join cte c on c.id = k.pid
)select id,name from cte;