关键字：SQL，CTE，递归查询
概述：通常递归查询是一个有难度的话题，尽管如此，它们仍使您能够完成在 SQL 中无法实现的操作。本文通过示例进行了简单介绍，并展示了与 PL/SQL的递归查询实现的差异。
一、公用表表达式（WITH子句）
公用表表达式（CTE）可以被看作是一个视图，只适用于一个单一的查询：
WITH ctename AS ( SELECT ... ) SELECT ... FROM ctename ...
这也可以写成 中的子查询FROM，但使用 CTE 有一些优点：
- 查询变得更具可读性。
- 您可以在查询中多次引用 CTE，并且只会计算一次。
- 您可以在 CTE 中使用数据修改语句（通常带有RETURNING子句）。
请注意，在 V8R3 ，总是物化 CTE。这意味着，CTE 是独立于包含查询计算的。从 V8R6 开始，CTE 可以“内联”到查询中，这提供了进一步的优化潜力。
二、递归查询的语法
递归查询是使用递归 CTE编写的，即包含RECURSIVE关键字的CTE ：
WITH RECURSIVE ctename AS ( SELECT /* non-recursive branch, cannot reference "ctename" */ UNION [ALL] SELECT /* recursive branch referencing "ctename" */ ) SELECT ... FROM ctename ...
三、如何处理递归查询
KingbaseES内部使用 WorkTable 来处理递归 CTE。这种处理并不是真正的递归，而是迭代：
首先，通过执行 CTE 的非递归分支来初始化WorkTable 。CTE 的结果也用这个结果集初始化。如果递归 CTE 使用UNION而不是UNION ALL，则删除重复的行。
然后，KingbaseES重复以下操作，直到WorkTable 为空：
- 评估 CTE 的递归分支，用WorkTable 替换对 CTE 的引用。
- 将所有结果行添加到 CTE 结果。如果UNION用于合并分支，则丢弃重复的行。
- 用上一步中的所有新行替换WorkTable （不包括任何已删除的重复行）。
请注意，到目前为止，CTE的自引用分支并未使用完整的 CTE 结果执行，而是仅使用自上次迭代（WorkTable ）以来的新行。
必须意识到这里无限循环的危险：如果迭代永远不会结束，查询将一直运行直到结果表变得足够大以导致错误。有两种方法可以处理：
- 通常，您可以通过使用 UNION来避免无限递归，这会删除重复的结果行（但当然需要额外的处理工作）。
- 另一种方法是LIMIT在使用 CTE 的查询上放置一个子句，因为如果递归 CTE 计算的行数与父查询获取的行数一样多，KingbaseES将停止处理。请注意，此技术不可移植到其他符合标准的数据库。
请看实际执行计划：
test=# explain WITH RECURSIVE ctename AS (
test(# SELECT empno, ename
test(# FROM emp
test(# WHERE empno = 7566
test(# UNION ALL
test(# SELECT emp.empno, emp.ename
test(# FROM emp JOIN ctename ON emp.mgr = ctename.empno
test(# )
test-# SELECT * FROM ctename;
--------------------------------------------------------------------------------------------------
CTE Scan on ctename (cost=417.62..489.74 rows=3606 width=36)
CTE ctename
-> Recursive Union (cost=0.00..417.62 rows=3606 width=36)
-> Seq Scan on emp (cost=0.00..25.00 rows=6 width=36)
Filter: (empno = 7566)
-> Hash Join (cost=1.95..32.05 rows=360 width=36)
Hash Cond: (emp_1.mgr = ctename_1.empno)
-> Seq Scan on emp emp_1 (cost=0.00..22.00 rows=1200 width=40)
-> Hash (cost=1.20..1.20 rows=60 width=4)
-> WorkTable Scan on ctename ctename_1 (cost=0.00..1.20 rows=60 width=4)
四、一个简单的例子
让我们假设一个像这样的自引用表
TABLE emp; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+-----------+------+------------+---------+---------+-------- 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 (12 rows)
我们要查找人员 7566 的所有下属，包括人员本身。查询的非递归分支将是：
SELECT empno, ename FROM emp WHERE empno = 7566;
递归分支会找到WorkTable中所有条目的所有下级：
SELECT emp.empno, emp.ename FROM emp JOIN ctename ON emp.mgr = ctename.empno;
可以假设依赖项不包含循环（没有人是他或她自己的经理，直接或间接）。所以可以将查询与 UNION ALL 结合起来，因为不会发生重复。所以完整查询将是：
WITH RECURSIVE ctename AS (
SELECT empno, ename
FROM emp
WHERE empno = 7566
UNION ALL
SELECT emp.empno, emp.ename
FROM emp JOIN ctename ON emp.mgr = ctename.empno
)
SELECT * FROM ctename;
empno | ename
-------+-------
7566 | JONES
7902 | FORD
7369 | SMITH
(3 rows)
五、添加生成的列
有时您想添加更多信息，例如层级。您可以通过将起始级别添加为非递归分支中的常量来实现。在递归分支中，您只需将 1 添加到级别：
WITH RECURSIVE ctename AS (
SELECT empno, ename,
0 AS level
FROM emp
WHERE empno = 7566
UNION ALL
SELECT emp.empno, emp.ename,
ctename.level + 1
FROM emp
JOIN ctename ON emp.mgr = ctename.empno
)
SELECT * FROM ctename;
empno | ename | level
-------+-------+-------
7566 | JONES | 0
7902 | FORD | 1
7369 | SMITH | 2
(3 rows)
如果UNION在循环引用的情况下使用避免重复行，则不能使用此技术。这是因为添加level会使之前相同的行不同。但在那种情况下，分层级别无论如何都没有多大意义，因为一个条目可能出现在无限多个级别上。
另一个常见的要求是收集“路径”中的所有祖先：
WITH RECURSIVE ctename AS (
SELECT empno, ename,
ename AS path
FROM emp
WHERE empno = 7566
UNION ALL
SELECT emp.empno, emp.ename,
ctename.path || ‘ -> ‘ || emp.ename
FROM emp
JOIN ctename ON emp.mgr = ctename.empno
)
SELECT * FROM ctename;
empno | ename | path
-------+-------+------------------------
7566 | JONES | JONES
7902 | FORD | JONES -> FORD
7369 | SMITH | JONES -> FORD -> SMITH
六、与 PLSQL 的比较
PLSQL对于不符合 SQL 标准的递归查询有不同的语法。原始示例如下所示：
SELECT empno, ename
FROM emp
START WITH empno = 7566
CONNECT BY PRIOR empno = mgr;
EMPNO ENAME
---------- ----------
7566 JONES
7902 FORD
7369 SMITH
这种语法更简洁，但不如递归 CTE 强大。对于涉及连接的更复杂的查询，它可能变得困难和混乱。将 PLSQL “分层查询”转换为递归 CTE 总是很容易的：
- 非递归分支是不带CONNECT BY子句但包含START WITH子句的 Oracle 查询。
- 递归分支是不带START WITH子句但包含CONNECT BY子句的 Oracle 查询。添加具有递归 CTE 名称的PRIOR联接，并用来自该联接 CTE 的列替换所有列。
- 如果 Oracle 查询使用CONNECT BY NOCYCLE，则使用UNION，否则使用UNION ALL。
一般把connect by语法称为递归查询，然而严格来说这是一个错误的叫法。因为它无法把当前层所计算得到的值传递到下一层，所以对它的称呼都是Hierarchical Queries in Oracle (CONNECT BY) 。
七、递归查询的真正实力
如果没有递归 CTE，很多可以用过程语言编写的东西就不能用 SQL 编写。这通常影响数据库的使用，因为 SQL 是用来查询数据库的。但是递归 CTE 使 SQL过程代码更完善，也就是说，它可以执行与任何其他编程语言相同的计算。前面的示例表明递归 CTE 可以完成您在 SQL 中无法执行的有用工作。
作为递归查询功能的示例，这里是一个递归 CTE，它计算斐波那契数列的第一个元素：
WITH RECURSIVE t(n,last_n,cnt) AS (
SELECT 1,0,1 FROM DUAL
UNION ALL
SELECT t.n+t.last_n, t.n, t.cnt+1
FROM t
)
SELECT * FROM T limit 10
n | last_n | cnt
----+--------+-----
1 | 0 | 1
1 | 1 | 2
2 | 1 | 3
3 | 2 | 4
5 | 3 | 5
8 | 5 | 6
13 | 8 | 7
21 | 13 | 8
34 | 21 | 9
55 | 34 | 10