巧用 DB2 递归 SQL

爱是王道

开始之前

递归 SQL 是在关系数据库中解析层次结构数据的非常有效手段。它可以用于高效地查询组织架构、零件表单、定单系统、网络结构等层次型数据。虽然递归 SQL 的语法较一般 SQL 要复杂一些，但只要理解了其基本原理和几个基本组成部分，程序员也不难写出巧妙的递归 SQL 来代替繁复冗长的应用程序代码。

本文将介绍递归 SQL 的语法，工作原理及其在层次型数据查询方面的应用实例，以帮助数据库程序员简化程序，提高效率。除了查询层次结构数据，递归 SQL 还可以应用在其他方面，本文也将介绍其在构造测试数据方面的巧妙应用。


--------------------------------------------------------------------------------
回页首
DB2 递归 SQL 的语法

递归 SQL 在 DB2 中通过公共表表达式 (CTE，Common Table Expression) 来实现。递归 SQL 由递归 CTE 以及对递归 CTE 结果的查询组成。那什么是递归 CTE 呢？简言之，如果 CTE 中的 FULLSELECT 在 FROM 子句中引用到 CTE 本身，就是递归 CTE。递归 CTE 包含以下三个组成部分：

·初始查询
初始查询是 CTE 中对基本表进行查询的部分。CTE 定义中的第一个 FULLSELECT 必须不包含对 CTE 自身的应用，即必须是初始查询。

·递归查询
递归查询就是通过对 CTE 自身的引用，从而启动递归逻辑的查询。递归查询需要遵循以下几个规则 ：

1.递归查询和初始查询结果必须包含相同数量的数据列；
2.递归查询和初始查询结果数据列的、长度等必须一致；
3.递归查询不能包含 GROUP BY 或者 HAVING 子句；
4.递归查询不能包含 Outer Join；
5.递归查询不能包含子查询 (Subquery)；
6.递归查询必须用 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 所示为清单 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 表节点层次结构


理解了递归 CTE 的工作原理 , 我们再用一个更为实际的例子来展示递归 CTE 在有层次关系的数据库表中的各种灵活应用， 看看以往需要通过多次查询和大量应用程序代码才能实现的功能 , 是如何通过一个简单的递归 CTE 完成的。


--------------------------------------------------------------------------------
回页首
层次型数据递归查询应用

对于层次型的数据，使用递归 SQL 查询十分方便，以下示例将基于如图 3 所示的组织架构图。


图 3. 人员组织结构管理层次结构示例


首先执行清单 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 管理的所有员工


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 的报告链


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 还有一个特性，就是它可以从一行数据递归产生多行数据。利用这个特性，递归 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( ) 函数，产生对应各分区的随机数据。


--------------------------------------------------------------------------------
回页首
结束语

阅读完本文，读者应该能够：

1.理解 DB2 中递归 SQL 的语法和工作原理；
2.利用递归 SQL 查询具有层次关系的数据；
3.利用递归 SQL 构造具有同样属性的测试数据。
DB2 递归 SQL 为处理层次型数据提供了非常有效的解决方法。通过使用 DB2 递归 SQL，对于特定类型的问题，我们可以简化应用程序，极大地提高程序运行效率。本文还讨论了递归 SQL 在构造测试数据方面的应用，希望能启发读者将这项技术灵活运用到更多新的领域。


http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1010liush/