PostgreSQL=>递归查询

PostgreSQL=>递归查询

转载请注明源地址:http://www.cnblogs.com/funnyzpc/p/8232073.html

  距上次博客更新刚好两周,这两周发生了很多,比如:SFTP服务拉取数据,第三方公共平台接口逻辑迁移新框架,新框架(Spring Cloud)上手,公司月报和审计数据获取等等。。。,差不多都有无尽的坑,尤其是最后者,实是折腾人啊

~;牢骚归牢骚,但是事情还是要认真做的,。。。,就目前来看,这些对于我最大的好处就是有助于快速理解公司业务逻辑

;啊哈~,扯完,从这些日子开始抽周末时间学习数据库->PosgreSQL(个人惯称:大象

),遂从本节起说PostgreSQL有关的动西。

  记得在上一家公司的时候做过一个冷门的附加功能,就是把根据传入的部门ID(一个List)查找部门下所有的人员,当时是Oracle数据库配合着Mybatis来做的,中间填过两个坑,一个是Mybatis的forach的参数个数超过1K会报错,导致递归不能查询,另一个坑是Oracle的递归造型稍难,这个。。。我翻了几篇博客写了好几行注释加以理解,希望后来人能明白前人的良苦用心。由于新买MBP 未装Oracle环境,oracle的递归讲解就此略过哈(◡‿◡✿)o~

  首先给出一个测试表(elevel) 关于职称级别的表,一位数的ID是最大分类(英语、计算机、会计),然后子级别的parent_id字段引用父级ID,有些级别比较笼统这里不讨论哈~:

p.p1 { margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Menlo; color: #f4f4f4; background-color: #000000 } span.s1 { }

testDB=> select * from elevel order by  rpad(id::varchar,5,'0');

  id  |         name         | parent_id

------+----------------------+-----------

    1 | 英语                 |

   11 | 英语专业四八级       |         1

  111 | 英语专业四级         |        11

  112 | 英语专业八级         |        11

   12 | 大学英语三、四、六级 |         1

  121 | 大学英语三级         |        12

  122 | 大学英语四级         |        12

  123 | 大学英语六级         |        12

    2 | 计算机               |

   21 | NCR计算机等级        |         2

  211 | NCR计算机一级        |        21

  212 | NCR计算机二级        |        21

  213 | NCR计算机三级        |        21

  214 | NCR计算机四级        |        21

   22 | IT认证类考试         |         2

  221 | CISCO认证            |        22

  222 | ORACLE认证           |        22

    3 | 会计                 |

   31 | 会计从业证           |         3

   32 | 会计职称             |         3

  321 | 初级职称(助理会计师) |        32

  322 | 中级职称(会计师)     |        32

  323 | 高级职称(高级职称)   |        32

 3231 | 正高级会计师         |       323

 3232 | 副高级会计师         |       323

(25 rows)

  数据造型已经给出了,这里我放出建表语句及测试数据:

 1 -- create table 
 2 CREATE TABLE elevel
 3 (
 4    id          integer,
 5    "name"      CHARACTER VARYING (20),
 6    parent_id   integer
 7 );
 8 
 9 -- insert data
10      INSERT INTO elevel (id, "name", parent_id) VALUES (1, '英语', NULL);
11      INSERT INTO elevel (id, "name", parent_id) VALUES (2, '计算机', NULL);
12      INSERT INTO elevel (id, "name", parent_id) VALUES (3, '会计', NULL);
13      INSERT INTO elevel (id, "name", parent_id) VALUES (11, '英语专业四八级', 1);
14      INSERT INTO elevel (id, "name", parent_id) VALUES (111, '英语专业四级', 11);
15      INSERT INTO elevel (id, "name", parent_id) VALUES (112, '英语专业八级', 11);
16      INSERT INTO elevel (id, "name", parent_id) VALUES (121, '大学英语三级', 12);
17      INSERT INTO elevel (id, "name", parent_id) VALUES (122, '大学英语四级', 12);
18      INSERT INTO elevel (id, "name", parent_id) VALUES (12, '大学英语三、四、六级', 1);
19      INSERT INTO elevel (id, "name", parent_id) VALUES (123, '大学英语六级', 12);
20      INSERT INTO elevel (id, "name", parent_id) VALUES (21, 'NCR计算机等级', 2);
21      INSERT INTO elevel (id, "name", parent_id) VALUES (22, 'IT认证类考试', 2);
22      INSERT INTO elevel (id, "name", parent_id) VALUES (211, 'NCR计算机一级', 21);
23      INSERT INTO elevel (id, "name", parent_id) VALUES (212, 'NCR计算机二级', 21);
24      INSERT INTO elevel (id, "name", parent_id) VALUES (213, 'NCR计算机三级', 21);
25      INSERT INTO elevel (id, "name", parent_id) VALUES (214, 'NCR计算机四级', 21);
26      INSERT INTO elevel (id, "name", parent_id) VALUES (221, 'CISCO认证', 22);
27      INSERT INTO elevel (id, "name", parent_id) VALUES (222, 'ORACLE认证', 22);
28      INSERT INTO elevel (id, "name", parent_id) VALUES (31, '会计从业证', 3);
29      INSERT INTO elevel (id, "name", parent_id) VALUES (32, '会计职称', 3);
30      INSERT INTO elevel (id, "name", parent_id) VALUES (321, '初级职称(助理会计师)', 32);
31      INSERT INTO elevel (id, "name", parent_id) VALUES (322, '中级职称(会计师)', 32);
32      INSERT INTO elevel (id, "name", parent_id) VALUES (323, '高级职称(高级职称)', 32);
33      INSERT INTO elevel (id, "name", parent_id) VALUES (3231, '正高级会计师', 323);
34      INSERT INTO elevel (id, "name", parent_id) VALUES (3232, '副高级会计师', 323);
35      COMMIT;

  现在我定一个需求:查询“会计”(id=3)类别下的所有的子记录(包含id=3的记录):

1 WITH RECURSIVE le (id,name,parent_id) as 
2 (
3  select id,name,parent_id from elevel where id=3
4  union all
5  select e2.id,e2.name,e2.parent_id from elevel e2,le e3 where e3.id=e2.parent_id 
6 )
7  select * from le order by rpad(id::varchar,5,'0') asc;

查询结果: 

p.p1 { margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Menlo; color: #f4f4f4; background-color: #000000 } span.s1 { }

  id  |         name         | parent_id

------+----------------------+-----------

    3 | 会计                 |

   31 | 会计从业证           |         3

   32 | 会计职称             |         3

  321 | 初级职称(助理会计师) |        32

  322 | 中级职称(会计师)     |        32

  323 | 高级职称(高级职称)   |        32

 3231 | 正高级会计师         |       323

 3232 | 副高级会计师         |       323

(8 rows)

根据以上查询结果,这里敲黑板,划重点

=>“RECURSIVE” 是PostgreSQL的关键字不是具体存在的表

  =>第一行中的:"(id,name,parent_id)"定义的是虚拟el表的参数,字段的名称可随意,但字段的个数一定要与3~5行中的查询结果的个数一致!

  =>"el"是声明的虚拟表,每次递归一层后都会将本层数据写入el中

  =>第三行中的id=3是需要查询开始层的ID,关键是第五行=>需要将虚拟表“el"表与“elevel”实体表连表查询

  =>特别需要注意的是第三行中的中的where条件(e3.id=e2.parent_id) ,取虚拟表的ID和实体表parent_id连

    这个条件决定了当前递归查询的查询方式(向上查询还是向下查询);

  =>第三行的递归开始查询不可缺少,不然查询报错,个人理解这是PostgreSQL根据首行的记录来递归子记录

好了,需要总结的大概就是这些,至于第七行中的rpad函数是向右补齐的函数,用于排序的需要,读者可以略去order by之后的内容。

  好了,一个简单的递归查询就成了,嗯。。。,如需求同学说:我需要将每条记录的递归结构(path)和层级(depath)的顺序都显示出来。

  遗憾的是PG递归查询本身并没有提供相应的函数和关键字来方便我们的需求,怎么办=>加字段:

1 with RECURSIVE le (id,name,parent_id,path,depath) as 
2 (
3  select id,name,parent_id,Array[id] as path,1 as depath from elevel where id=3
4  union all
5  select e2.id,e2.name,e2.parent_id,e3.path||e3.id,e3.depath+1 
6      from elevel e2,le e3 where e3.id=e2.parent_id 
7 )
8 select * from le order by rpad(id::varchar,5,'0') asc;

查询结果:

  id  |         name         | parent_id |     path     | depath

------+----------------------+-----------+--------------+--------

    3 | 会计                 |           | {3}          |      1

   31 | 会计从业证           |         3 | {3,3}        |      2

   32 | 会计职称             |         3 | {3,3}        |      2

  321 | 初级职称(助理会计师) |        32 | {3,3,32}     |      3

  322 | 中级职称(会计师)     |        32 | {3,3,32}     |      3

  323 | 高级职称(高级职称)   |        32 | {3,3,32}     |      3

 3231 | 正高级会计师         |       323 | {3,3,32,323} |      4

 3232 | 副高级会计师         |       323 | {3,3,32,323} |      4

(8 rows)

  嗯~,可以看到查询SQL与之上的查询不同的是第三行中定义了一个"Array[id]" 的递归结构字段,最为和一个“1” 的深度字段,Array函数是PostgreSQL特有的数组函数,读者可以自行查阅资料了解哈( ^)o(^ )~。

  当然以上查询语句满足既已有的需求,想下->如果这里变我最成最初我做过的那个需求(查询部门下的所有人,不含部门记录),该怎么办呢。

  额~,递归本身提供给我们的结果已经趋于完美了,由于官方api并没有提供进一步的方法,这里只有从查询结果着手解决这个问题囖

~

with RECURSIVE le (id,name,parent_id,path,depath) as 
(
 select id,name,parent_id,Array[id] as path,1 as depath from elevel where id=3
 union all
 select e2.id,e2.name,e2.parent_id,e3.path||e3.id,e3.depath+1 
     from elevel e2,le e3 where e3.id=e2.parent_id 
)
select * from le l where 0=(select count(1) from le where parent_id=l.id) order by rpad(id::varchar,5,'0') asc;

查询结果:

p.p1 { margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Menlo; color: #f4f4f4; background-color: #000000 } span.s1 { }

  id  |         name         | parent_id |     path     | depath

------+----------------------+-----------+--------------+--------

   31 | 会计从业证           |         3 | {3,3}        |      2

  321 | 初级职称(助理会计师) |        32 | {3,3,32}     |      3

  322 | 中级职称(会计师)     |        32 | {3,3,32}     |      3

 3231 | 正高级会计师         |       323 | {3,3,32,323} |      4

 3232 | 副高级会计师         |       323 | {3,3,32,323} |      4

(5 rows)

 根据以上查询SQL来看,答案其实很简单,在递归完成后将存在子记录的用where条件过滤掉即可(见查询语句最后一行)

嗯,以上几个例子全部是向下递归查询,下面我展示下向上查询的语句,很简单=>

1  with RECURSIVE le (id,name,parent_id) as 
2  (
3   select id,name,parent_id from elevel where id=323
4   union all
5   select e2.id,e2.name,e2.parent_id from elevel e2,le e3 where e3.parent_id=e2.id
6  )
7  select * from le order by rpad(id::varchar,5,'0') asc;

查询结果:

p.p1 { margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Menlo; color: #f4f4f4; background-color: #000000 } span.s1 { }

 id  |        name        | parent_id

-----+--------------------+-----------

   3 | 会计               |

  32 | 会计职称            |        3

 323 | 高级职称(高级职称)   |        32

可以看到与向上查询的查询语句相差不几,关键,关键是=>第5行的where条件,很意外吧,如此小的改动就有查询方向上的变化,个人对此的理解是:

 =>递归向下查询是用虚拟表的id去联结递归表的parent_id

  =>递归向上查询是用虚拟表的parent_id去联结递归表的id

  本人愚钝,目前对于两者的区别发现仅限于此,欢迎读者点拨哈

。。。

~

最后,需要说明的是,在公司业务满足的情况下尽可能用单层查询语句查询,尤其对于层级较少较固定的结构下较为合适,此建议主要针对的是递归的两大问题而言:

 1>递归的查询效率较低,尤其是记录较多层级庞大的记录

  2>若现有记录的层级如有交叉,极容易导致递归死循环,这点尤其要注意

OK, 本节完成,下节开始讲:“窗口函数

现在是:2018-01-21 21:20:50,愿各位晚安,明天要上班哦~

p.p1 { margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Menlo; color: #f4f4f4; background-color: #000000 } span.s1 { }

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏PPV课数据科学社区

【学习】图解SQL连接语句

SQL 联合语句好像是基于集合的,用韦恩图来解释咋一看是很自然而然的。假设我们有下面两张表。表A在左边,表B在右边。我们给它们各四条记录。 id name ...

3399
来自专栏企鹅号快讯

Access/VBA/Excel-左连接-11

系统:Windows 7 软件:Excel 2010/Access 2010 这个系列开展一个新的篇章,重点关注Access数据库 主体框架:以Excel作为操...

1857
来自专栏杨建荣的学习笔记

最近的几个技术问题总结和答疑(九)(r10笔记第16天)

最近的琐事比较多,而提问题的朋友还是不少,很多消息都没有来得及回复,各种事情一堆起来,不少问题想起来已经过了好几天了,所以还是来整理一篇技术问答为好。 ...

3264
来自专栏Hadoop实操

Hive使用十六进制分隔符异常分析

通过sqoop抽取Mysql表数据到hive表,发现hive表所有列显示为null

64211
来自专栏互联网开发者交流社区

SQL基础用法(实例二)

1227
来自专栏生信技能树

没有自己的服务器如何学习生物数据分析(下篇)

编者注:在上篇文章《没有自己的服务器如何学习生物数据分析》上篇,我们对 IBM 云计算平台有了基本了解,也学习了如何对数据进行下载上传以及基本的预处理。 在《没...

2977
来自专栏Python数据科学

【SQL刷题系列】:leetcode183 Customers Who Never Order

假设一个网站包含两个表: Customers和Orders。写出一个SQL查询语句找出所有没有任何订单的顾客。

672
来自专栏HansBug's Lab

1639: [Usaco2007 Mar]Monthly Expense 月度开支

1639: [Usaco2007 Mar]Monthly Expense 月度开支 Time Limit: 5 Sec  Memory Limit: 64 MB...

3176
来自专栏Alan's Lab

[From Nand to Tetris] 第8章 虚拟机项目 python 实现

为防闲逛至此的看官不知所云: From Nand to Tetris 是一个在线课程,目标是指导学生从 Nand 逻辑门开始从头到尾完成一整套计算机系统。

452
来自专栏影子

PostgreSQL=>递归查询

3218

扫码关注云+社区