PostgresSQL递归查询实现省市级联


最近跟同事学来一招,可以利用PostgreSQL的递归功能轻松实现一张表的省市级联的效果,同样,也可以利用该功能实现多重菜单递归查询的功能。虽然使用程序也可以实现这样的功能,但势必影响性能。

 PostgreSQL提供了一个with 的递归功能,将递归后的结果放入这个递归表中,再用递归表进行查询,再次放入,直到实现完整的查询跳出查询。

如求1100的和  

[c]

WITH RECURSIVE t(n) AS (
VALUES (1)
union ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

[/c]

结果:5050

下面在举个例子,我在网上扒了个inert构成省市级联数据表。

[c]

create table base.tb_city(id varchar(3) , pid varchar(3) , name varchar(10));
insert into base.tb_city values(’002′ , 0 , ‘浙江省’);
insert into base.tb_city values(’001′ , 0 , ‘广东省’);
insert into base.tb_city values(’003′ , ’002′ , ‘衢州市’);
insert into base.tb_city values(’004′ , ’002′ , ‘杭州市’) ;
insert into base.tb_city values(’005′ , ’002′ , ‘湖州市’);
insert into base.tb_city values(’006′ , ’002′ , ‘嘉兴市’) ;
insert into base.tb_city values(’007′ , ’002′ , ‘宁波市’);
insert into base.tb_city values(’008′ , ’002′ , ‘绍兴市’) ;
insert into base.tb_city values(’009′ , ’002′ , ‘台州市’);
insert into base.tb_city values(’010′ , ’002′ , ‘温州市’) ;
insert into base.tb_city values(’011′ , ’002′ , ‘丽水市’);
insert into base.tb_city values(’012′ , ’002′ , ‘金华市’) ;
insert into base.tb_city values(’013′ , ’002′ , ‘舟山市’);
insert into base.tb_city values(’014′ , ’004′ , ‘上城区’) ;
insert into base.tb_city values(’015′ , ’004′ , ‘下城区’);
insert into base.tb_city values(’016′ , ’004′ , ‘拱墅区’) ;
insert into base.tb_city values(’017′ , ’004′ , ‘余杭区’) ;
insert into base.tb_city values(’018′ , ’011′ , ‘金东区’) ;
insert into base.tb_city values(’019′ , ’001′ , ‘广州市’) ;
insert into base.tb_city values(’020′ , ’001′ , ‘深圳市’) ;
———-
[/c]

效果如下:
2015-03-26 09:51:07屏幕截图

下面利用RECURSIVE功能实现查询检索。

[c]
with RECURSIVE cte as
(
select a.id, a.pid, cast(a.name as varchar(100)) from base.tb_city a where id=’002′
union all
select k.id, k.pid, cast(c.name||’ > ‘||k.name as varchar(100)) as name from base.tb_city k inner join cte c on c.id = k.pid
)
select id, pid, name from cte ;

[/c]

最终效果如下:

2015-03-26 09:51:44 的屏幕截图

Reference

postgresql with 递归查询

 


分享到:

2 条评论

昵称
  1. http://www.xmten.com/

    不错的文章,内容一针见血.

  2. http://www.xmten.com/

    好文章,内容排山倒海.