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 { }
腾讯云开发者
