递归 SQL 在 DB2 中通过公共表表达式 (CTE,Common Table Expression) 来实现。递归 SQL 由递归 CTE 以及对递归 CTE 结果的查询组成。那什么是递归 CTE 呢?简言之,如果 CTE 中的 FULLSELECT 在 FROM 子句中引用到 CTE 本身,就是递归 CTE。递归 CTE 包含以下三个组成部分:
- 初始查询
初始查询是 CTE 中对基本表进行查询的部分。CTE 定义中的第一个 FULLSELECT 必须不包含对 CTE 自身的应用,即必须是初始查询。
- 递归查询
递归查询就是通过对 CTE 自身的引用,从而启动递归逻辑的查询。递归查询需要遵循以下几个规则 :
- 递归查询和初始查询结果必须包含相同数量的数据列;
- 递归查询和初始查询结果数据列的、长度等必须一致;
- 递归查询不能包含 GROUP BY 或者 HAVING 子句;
- 递归查询不能包含 Outer Join;
- 递归查询不能包含子查询 (Subquery);
- 递归查询必须用 UNION ALL 联结。
- 终止条件
终止条件通常是隐性的,即如果前一次递归查询返回的结果集为空,则终止递归;但是也可以在递归查询中设定终止条件,如限定递归查询的深度等。
下面我们用一个简单的例子来说明初始查询,递归查询和终止条件是如何实现一个递归 CTE 的。
以下通过一个描述节点层次关系的实例来说明递归 SQL 的工作原理。
首先执行清单 1 中的 SQL 语句来建立该实例所用的表和数据。
清单 1. 创建 NODE 表和数据
CREATE TABLE NODE( CHILD INTEGER NOT NULL, PARENT INTEGER NOT NULL); INSERT INTO NODE VALUES(1, 0); INSERT INTO NODE VALUES(2, 6); INSERT INTO NODE VALUES(3, 1); INSERT INTO NODE VALUES(4, 5); INSERT INTO NODE VALUES(5, 3); INSERT INTO NODE VALUES(6, 3); INSERT INTO NODE VALUES(7, 5); INSERT INTO NODE VALUES(8, 5); |
成功执行清单 1 中的 SQL 后,NODE 表的内容如表 1 所示。
表 1. NODE 表
child | parent |
---|---|
1 | 0 |
2 | 6 |
3 | 1 |
4 | 5 |
5 | 3 |
6 | 3 |
7 | 5 |
8 | 5 |
则清单 2 中的 SQL 将得出 NODE 表的层次结构。
清单 2. NODE 表层次结构查询
WITH report(parent, child) AS ( SELECT parent, child FROM node WHERE parent = 0 UNION ALL SELECT b.parent, b.child FROM report a, node b WHERE b.parent = a.child ) SELECT * FROM report; |
图 1. NODE 表层次结构查询递归 SQL 的执行路径图
![图 1. NODE 表层次结构查询递归 SQL 的执行路径图](https://i-blog.csdnimg.cn/blog_migrate/05077c1c36e5db0ad1be6baf493d03c8.png)
图 1 所示为清单 2 中查询的执行路径图。QB3 为初始查询,QB4 为递归查询。
运行步骤:
1 . 初始查询返回初始结果集,这个查询返回的就是头节点,如表 2 所示。
表 2. 步骤 1 结果
parent | child |
---|---|
0 | 1 |
2 . 递归查询使用初始结果集作为 report CTE 的内容通过 node.parent = report.child 连接 NODE 表得到下一个结果集,也就是头节点 1 的子节点,如表 3 所示。
表 3. 步骤 2 结果
parent | child |
---|---|
1 | 3 |
3 . 递归查询迭代使用第 2 步的结果集作为 report CTE 的输入,继续连接 NODE 表得到节点 3 的子节点,如表 4 所示。
表 4. 步骤 3 结果
parent | child |
---|---|
3 | 5 |
3 | 6 |
4 . 使用第 3 步的结果集继续迭代,取得下一个结果集,如表 5 所示。
表 5. 步骤 4 结果
parent | child |
---|---|
5 | 4 |
5 | 7 |
5 | 8 |
6 | 2 |
5 . 使用第 4 步的结果集连接 NODE 表,返回为空,递归查询终止。最终返回结果为以上所有步骤中得到的结果集的 UNION,如表 6 所示。
表 6. 步骤 5 结果
parent | child |
---|---|
0 | 1 |
1 | 3 |
3 | 5 |
3 | 6 |
5 | 4 |
5 | 7 |
5 | 8 |
6 | 2 |
这样就可以清楚的得到图 2 所示的层次结构。
图 2. NODE 表节点层次结构
![图 2. NODE 表节点层次结构](https://i-blog.csdnimg.cn/blog_migrate/49b0fc0c4a59b6ba213ddfbdc78db660.png)
理解了递归 CTE 的工作原理 , 我们再用一个更为实际的例子来展示递归 CTE 在有层次关系的数据库表中的各种灵活应用, 看看以往需要通过多次查询和大量应用程序代码才能实现的功能 , 是如何通过一个简单的递归 CTE 完成的。
对于层次型的数据,使用递归 SQL 查询十分方便,以下示例将基于如图 3 所示的组织架构图。
图 3. 人员组织结构管理层次结构示例
![图 3. 人员组织结构管理层次结构示例](https://i-blog.csdnimg.cn/blog_migrate/0dd01ed9f4384322c31f0ce5b6789438.png)
首先执行清单 3 中的 SQL 语句来建立表和数据。
清单 3. 创建 ORG 表和数据
CREATE TABLE ORG( EMPID INTEGER NOT NULL, EMPNAME VARCHAR(128) NOT NULL, MGRID INTEGER NOT NULL); INSERT INTO ORG VALUES(1, 'Jack', 0); INSERT INTO ORG VALUES(2, 'Mary', 1); INSERT INTO ORG VALUES(3, 'Tom', 1); INSERT INTO ORG VALUES(4, 'Ben', 2); INSERT INTO ORG VALUES(5, 'John', 3); INSERT INTO ORG VALUES(6, 'Emily', 3); INSERT INTO ORG VALUES(7, 'Kate', 3); INSERT INTO ORG VALUES(8, 'Mark', 6); |
此时,ORG 表内容如表 7 所示。
表 7. ORG 表
Empid | Empname | Mgrid |
---|---|---|
1 | Jack | 0 |
2 | Mary | 1 |
3 | Tom | 1 |
4 | Ben | 2 |
5 | John | 3 |
6 | Emily | 3 |
7 | Kate | 3 |
8 | Mark | 6 |
1. 从上往下的查询,列出 Tom 管理的所有员工的名字。对应的递归 SQL 如清单 4 所示。
清单 4. 查询 Tom 管理的所有员工
WITH report(empid,empname) AS ( SELECT empid, empname FROM org WHERE mgrid = 3 UNION ALL SELECT a.empid, a.empname FROM org a, report b WHERE a.mgrid= b.empid ) SELECT empname FROM report; |
执行结果如表 8、图 4 所示。
表 8. Tom 管理的所有员工
EMPNAME |
---|
John |
Emily |
Kate |
Mark |
图 4. Tom 管理的所有员工
![图 4. Tom 管理的所有员工](https://i-blog.csdnimg.cn/blog_migrate/ce2a62350782500ef9d5a2fce933abd9.png)
2. 从下往上的查询,列出 Mark 的报告链。对应的递归 SQL 如清单 5 所示。
清单 5. 查询 Mark 的报告链
WITH report(empid,empname,mgrid) AS ( SELECT empid, empname,mgrid FROM org WHERE empid = 8 UNION ALL SELECT a.empid, a.empname, a.mgrid FROM org a, report b WHERE a.empid= b.mgrid ) SELECT empname FROM report; |
执行结果如表 9、图 5 所示。
表 9. Mark 的报告链
EMPNAME |
---|
Mark |
Emily |
Tom |
Jack |
图 5. Mark 的报告链
![图 5. Mark 的报告链](https://i-blog.csdnimg.cn/blog_migrate/0cd224230b94a1d22f0522d93a30943f.png)
3. 使用 level 列控制递归深度。递归 SQL 可能造成循环,在 CTE 定义中设置一个 level 列来控制深度,使递归提前终止是常用的避免循环的做法。同时 level 列还可以表明层次结构中的层数。比如修改本例的 SQL,加入 level 列,我们可以看到 Jack 共管理了几级人员,对应的递归 SQL 如清单 6 所示。
清单 6. 查询 Jack 管理的层数
WITH report(level, empid, empname) AS ( SELECT 0, empid, empname FROM org WHERE empname = 'Jack' UNION ALL SELECT level+1, a.empid, a.empname FROM org a, report b WHERE a.mgrid= b.empid ) SELECT max(level) AS MAX_LEVEL FROM report; |
执行结果如表 10 所示。
表 10. Jack 管理的层数
MAX_LEVEL |
---|
3 |
或者我们可以修改 SQL,查询 Mark 的上级以及上上级经理是谁,对应的递归 SQL 如清单 7 所示。
清单 7. 查询 Mark 的上层经理
WITH report(level, empid, empname, mgrid) AS ( SELECT 0, empid, empname,mgrid FROM org WHERE empname = 'Mark' UNION ALL SELECT level+1, a.empid, a.empname, a.mgrid FROM org a, report b WHERE a.empid= b.mgrid ) SELECT level, empname FROM report WHERE level > 0; |
执行结果如表 11 所示。
表 11. Mark 的上层经理
LEVEL | EMPNAME |
---|---|
1 | Emily |
2 | Tom |
3 | Jack |
4. 汇总。汇总是计算总数,如果我们需要计算 Jack 管理的人员的总数,那只要把 SQL 修改成如清单 8 所示就可以了。
清单 8. 查询 Jack 管理的人数
WITH report(level, empid, empname) AS ( SELECT 0, empid, empname FROM org WHERE empname = 'Jack' UNION ALL SELECT level+1, a.empid, a.empname FROM org a, report b WHERE org.mgrid= report.empid ) SELECT COUNT(*) AS TOTAL_MANAGED FROM report WHERE level > 0; |
执行结果如表 12 所示。
表 12. Jack 管理的人数
TOTAL_MANAGED |
---|
7 |
但是我们如果要计算 ORG 表中所有人管理的人员总数,我们就需要从下往上进行汇总,对应的递归 SQL 如清单 9 所示。
清单 9. 查询所有人管理的人数
WITH report(empid, empname, mgrid) AS ( -- 选择 org 表中所有的行,即所有的员工 SELECT empid, empname, mgrid FROM org UNION ALL -- 对应前一次结果集的每一行,在新的结果集中为其经理插入一行 SELECT a.empid, a.empname, a.mgrid FROM org a, report b WHERE b.mgrid= a.empid ) -- 因为初始查询中每个员工都有初始行,所以最后结果要减去 1 SELECT empid, empname, COUNT(*)-1 AS TOTAL_MANAGED FROM report GROUP BY empid,empname; |
执行结果如表 13 所示。
表 13. 所有人管理的人数
EMPID | EMPNAME | TOTAL_MANAGED |
---|---|---|
1 | Jack | 7 |
2 | Mary | 1 |
3 | Tom | 4 |
4 | Ben | 0 |
5 | John | 0 |
6 | Emily | 1 |
7 | Kate | 0 |
8 | Mark | 0 |
递归 SQL 还有一个特性,就是它可以从一行数据递归产生多行数据。利用这个特性,递归 SQL 还可以用来构造特定类型的测试数据。
构造连续数据
假定需要构造一张时间表,包含一天的所有分钟,即表 DAY 有一列为 d_minute , 它的内容应该为:00:00:00 00:01:00 00:02:00 … … 23:58:00 23:59:00
对应的递归 SQL 如清单 10 所示。
清单 10. 构造连续数据
INSERT INTO DAY WITH temp(d_minute) AS ( SELECT TIME('00:00:00') FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT d_minute + 1 MINUTE FROM temp WHERE d_minute < TIME('23:59:00') ) SELECT * FROM temp; |
初始查询建立初始结果集,即从“00:00:00”开始,递归查询迭代给结果集里的数据一次加上 1 分钟,最终得到一整天的分钟数据。
构造阶乘数列
利用递归 SQL 可以非常简便地构造出阶乘数列。清单 11 利用递归 SQL 得出 1 到 10 的阶乘。
清单 11. 构造阶乘数列
WITH temp(LEVEL, RESULT) AS ( SELECT 1,1 FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT LEVEL+1,(LEVEL+1)*RESULT FROM temp WHERE LEVEL < 10 ) SELECT * FROM temp; |
执行结果如表 14 所示。
表 14.1 到 10 的阶乘数列
LEVEL | RESULT |
---|---|
1 | 1 |
2 | 2 |
3 | 6 |
4 | 24 |
5 | 120 |
6 | 720 |
7 | 5040 |
8 | 40320 |
9 | 362880 |
10 | 3628800 |
在这里一定要注意在递归查询中加入终止条件,不然 SQL 将无法退出迭代。
构造分区数据
假定 TEST 表是分区表,它以 key(integer) 列的值分为 10 个区,这 10 个区分别为 0~10000,10001~20000,20001 …… , 90000~10000。现在需要往 TEST 表里插入数据,并且希望每个分区都能插入 1000 行数据。可使用清单 12 中的递归 SQL 实现。
清单 12. 构造分区数据
INSERT INTO test WITH TEMP1 (NUM1) AS ( SELECT 0 FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT NUM1 + 1 FROM TEMP1 WHERE NUM1 < 9 ), TEMP2 (NUM1, NUM2) AS ( SELECT NUM1, 1 FROM TEMP1 UNION ALL SELECT NUM1, NUM2 + 1 FROM TEMP2 WHERE NUM2 < 1000 ), TEMP3 AS ( SELECT (NUM1 * 10000) + NUM2 AS NUM FROM TEMP2 ) SELECT NUM AS KEY FROM TEMP3; |
首先 CTE TEMP1 产生 0~9 对应 10 个分区;TEMP2 对应每个 TEMP1 的值产生 1~1000 共 1000 行数据;TEMP3 把 NUM1 放大再把两者相加,得到符合条件的数据。在 CTE TEMP2 中还可以使用 RAND( ) 函数,产生对应各分区的随机数据。