文档章节

PostgreSQL的递归查询(with recursive)

kenyon_君羊
 kenyon_君羊
发布于 2012/04/25 15:43
字数 680
阅读 18839
收藏 15

开发有需求,说需要对一张地区表进行递归查询,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' , 0 , '广东省'); 
insert into tb values('003' , '002' , '衢州市');  
insert into tb values('004' , '002' , '杭州市') ; 
insert into tb values('005' , '002' , '湖州市');  
insert into tb values('006' , '002' , '嘉兴市') ; 
insert into tb values('007' , '002' , '宁波市');  
insert into tb values('008' , '002' , '绍兴市') ; 
insert into tb values('009' , '002' , '台州市');  
insert into tb values('010' , '002' , '温州市') ; 
insert into tb values('011' , '002' , '丽水市');  
insert into tb values('012' , '002' , '金华市') ; 
insert into tb values('013' , '002' , '舟山市');  
insert into tb values('014' , '004' , '上城区') ; 
insert into tb values('015' , '004' , '下城区');  
insert into tb values('016' , '004' , '拱墅区') ; 
insert into tb values('017' , '004' , '余杭区') ; 
insert into tb values('018' , '011' , '金东区') ; 
insert into tb values('019' , '001' , '广州市') ; 
insert into tb values('020' , '001' , '深圳市') ;

测试语句,查询浙江省及以下县市:

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.name,a.pid from tb a where id='002' 
 union all  
 select k.id,k.name,k.pid  from tb k inner join cte c on c.id = k.pid 
 )select id,name from cte;

© 著作权归作者所有

共有 人打赏支持
kenyon_君羊
粉丝 499
博文 170
码字总数 121714
作品 0
杭州
其他
私信 提问
加载中

评论(9)

wowowowolj
wowowowolj
在mybatis里面怎么使用??
mark35
mark35

引用来自“dgdba”的评论

应该加入超过N层报错或舍去的参数。。SQL SERVER是有的

好像是可以添加一个条件判断来限制循环深度的
乌龟壳
乌龟壳
sqlserver也可以呀,后悔当年用临时表和循环实现的递归,如果早知道这个就好了。
不过想问下递归查询只是语法糖级别的,还是能真实在查询优化上体现出比循环更好的查询计划呢?
期待年轻
期待年轻
恩。通过这个方式,实现了一些功能
kenyon_君羊
kenyon_君羊

引用来自“放倒”的评论

引用来自“kenyon(君羊)”的评论

引用来自“dgdba”的评论

应该加入超过N层报错或舍去的参数。。SQL SERVER是有的

可否分享下SQL的这种参数?我试试在PG中行不行

可以使用 MAXRECURSION 提示以及在 INSERT、UPDATE、DELETE 或 SELECT 语句的 OPTION 子句中的一个 0 到 32,767 之间的值,来限制特定语句所允许的递归级数,以防止出现无限循环。 出处:http://msdn.microsoft.com/zh-cn/library/ms175972.aspx

MS-SQL中的例子在Postgres中一样适用的,也可以加入这类递归级数的参数。另外PG中出现死循环会直接报错的。
放倒
放倒

引用来自“kenyon(君羊)”的评论

引用来自“dgdba”的评论

应该加入超过N层报错或舍去的参数。。SQL SERVER是有的

可否分享下SQL的这种参数?我试试在PG中行不行

可以使用 MAXRECURSION 提示以及在 INSERT、UPDATE、DELETE 或 SELECT 语句的 OPTION 子句中的一个 0 到 32,767 之间的值,来限制特定语句所允许的递归级数,以防止出现无限循环。 出处:http://msdn.microsoft.com/zh-cn/library/ms175972.aspx
kenyon_君羊
kenyon_君羊

引用来自“dgdba”的评论

应该加入超过N层报错或舍去的参数。。SQL SERVER是有的

可否分享下SQL的这种参数?我试试在PG中行不行
d
dgdba
应该加入超过N层报错或舍去的参数。。SQL SERVER是有的
hyxj1220
hyxj1220
我之前一直都是用程序控制递归的,今天学习了之后,犹如醍醐灌顶,赞
PostgreSQL Oracle 兼容性 - connect by 2

标签 PostgreSQL , Oracle , 树形查询 , 递归 , connect by , tablefunc , connectby 背景 Oracle connect by语法经常用于有树形关系的记录查询,PostgreSQL使用CTE递归语法,可以实现同样的...

德哥
2018/10/05
0
0
PostgreSQL技术周刊第1期:PostgreSQL技术进阶群限时开放

PostgreSQL(简称PG)的开发者们: 云栖社区已有5000位PG开发者,发布了3000+PG文章(文章列表),沉淀了700+的PG精品问答(问答列表)。 从本周起我们开始发布PostgreSQL技术周刊,会介绍最...

不靠谱贝贝
2018/10/19
0
0
java ssh 项目oracle移植到postgresql

原来一使用ssh开发的项目,因实际需要,要从oracle迁移到postgresql。 理想 1.postgresql中创建相关表,把数据从oracle导到postgresql中 2.复制postgresql的jdbc包到项目中 3.配置jdbc参数,...

opal
2016/11/27
260
3
数据库案例集锦 - 开发者的《如来神掌》

标签 PostgreSQL , PG DBA cookbook , PG Oracle兼容性 , PG 架构师 cookbook , PG 开发者 cookbook , PG 应用案例 背景 「剑魔独孤求败,纵横江湖三十馀载,杀尽仇寇,败尽英雄,天下更无抗...

德哥
2017/06/09
0
0
PostgreSQL Indexes don't mean slow inserts - 索引改进PPT(写优化, lsm, Fractal, index buffer, COLA)

标签 PostgreSQL , LSM , index buffer , COLA , Cache-oblivious lookahead arrays , Fractal Tree , Covering and Unique 背景 https://wiki.postgresql.org/images/8/8b/WO-Indexes1.0.pd......

德哥
2018/10/05
0
0

没有更多内容

加载失败,请刷新页面

加载更多

java框架学习日志-13(Mybatis基本概念和简单的例子)

在mybatis初次学习Mybatis的时候,遇到了很多问题,虽然阿里云的视频有教学,但是视频教学所使用的软件和我自己使用的软件不用,我自己用的数据库是oracle数据库,开发环境是idea。而且视频中...

白话
今天
4
0
Java基础:String、StringBuffer和StringBuilder的区别

1 String String:字符串常量,字符串长度不可变。Java中String是immutable(不可变)的。 String类的包含如下定义: /** The value is used for character storage. */private final cha...

watermelon11
今天
3
0
mogodb服务

部署MongoDB 官网: https://www.mongodb.com/download-center/community 创建mongo数据目录 mkdir /data/mongodb 二进制部署 wget -c https://fastdl.mongodb.org/linux/mongodb-linux-x8......

以谁为师
昨天
5
0
大神教你Debian GNU/Linux 9.7 “Stretch” Live和安装镜像开放下载

Debian项目团队于昨天发布了Debian GNU/Linux 9 "Stretch" 的第7个维护版本更新,重点修复了APT软件管理器中存在的安全漏洞。在敦促每位用户尽快升级系统的同时,Debian团队还发布了Debian ...

linux-tao
昨天
4
0
PHP 相关配置

1. php-fpm的pool 编辑php-fpm配置文件php-fpm.con vim /usr/local/php/etc/php-fpm.conf //在[global]部分增加以下内容 include = etc/php-fpm.d/*.conf # 相当与Nginx的虚拟主机文件 “vho......

Yue_Chen
昨天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部