如何在 SQL 中写递归语句
MySQL 在 8.0 的版本引入了公共表表达式(Common Table Expressions),简称 CTE。CTE 在一些方面可以简化我们的 SQL 语句,让它看起来不至于太臃肿。
CTE 还可以用来写递归,这个在 MSSQL SERVER 上很早就能做到了。
先来看一个 Demo,使用递归生成 1 - 5 的数字系列。
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;
输出:
n
--------
1
2
3
4
5
递归表达式的语句有什么特点呢?
- 使用
WITH RECURSIVE
开头,关键词RECURSIVE
表明这段表达式是递归表达式;
- 自引用。递归的子查询有两部分,使用 [
UNION [ALL]
] 或 [UNION DISTINCT
] 分开。
SELECT ... # 返回初始数据集
UNION ALL
SELECT ... # 返回其它数据集
第二个SELECT
里面FROM
子句之后接的是 CTE 名称,即在这里它引用了自身,这也是实现递归的关键逻辑所在。
要检查一段递归表达式的是否存在问题,需要看这几个方面:
- 有初始数据集和边界条件,当达到了边界递归将不再继续;
- 正确的迭代表达式。
就拿刚才的 Demo 来说,它的初始数据集是 n = 1
,终止条件是 n < 5
,迭代的表达式是 n = n + 1
。如果没有终止条件或者表达式写得有问题(比如把n = n + 1
写成 n = n - 1
),SQL 直到超出了递归最大深度后才会终止。
递归表达式可以用来做什么呢?
- 生成斐波那契数列;
- 补全两个日期之间的缺失日期;
- 树形查询。
举一个递归实现树形查询的例子,还是拿 emp 表来说吧。我想知道 emp 表中每个员工的和 boss 之间的层级关系,以及员工所在的层级,使用递归就可以这么做:
- 先获取到 boss 的信息;
- 然后根据上下级关系不断去迭代,直到找到所有没有下级的员工的信息。
WITH RECURSIVE cte (empno, ename, LEVEL, tree) AS
(SELECT
empno,
ename,
0 AS LEVEL,
CAST(ename AS CHAR(120)) AS tree
FROM
emp
WHERE mgr IS NULL
UNION ALL
SELECT
e.empno,
e.ename,
c.level + 1,
CONCAT_WS('-->', e.ename, c.tree)
FROM
cte c
INNER JOIN emp e
ON e.mgr = c.empno)
SELECT
*
FROM
cte
这条 SQL 需要注意一个地方,我在递归子查询里面的第一个 SELECT
语句中指定了 tree
字段的长度。如果没有指定 tree
字段的长度,它将使用 ename
字段的实际长度作为 tree
字段的长度,在第二个 SELECT
子句中放入超过 tree
字段长度的内容将会被截断。
上面 SQL 执行的结果:
empno ename level tree
------ ------ ------ ------------------------------
7839 KING 0 KING
7566 JONES 1 JONES-->KING
7698 BLAKE 1 BLAKE-->KING
7782 CLARK 1 CLARK-->KING
7499 ALLEN 2 ALLEN-->BLAKE-->KING
7521 WARD 2 WARD-->BLAKE-->KING
7654 MARTIN 2 MARTIN-->BLAKE-->KING
7788 SCOTT 2 SCOTT-->JONES-->KING
7844 TURNER 2 TURNER-->BLAKE-->KING
7900 JAMES 2 JAMES-->BLAKE-->KING
7902 FORD 2 FORD-->JONES-->KING
7934 MILLER 2 MILLER-->CLARK-->KING
7369 SMITH 3 SMITH-->FORD-->JONES-->KING
7876 ADAMS 3 ADAMS-->SCOTT-->JONES-->KING
这篇文章没有涉及到或者讲得不深但我觉得又比较重要的地方:
- 字符串类型的字段的长度在非递归部分指定。因此,在递归子查询中,如果某个字段(字符串类型),在递归部分的长度会比超出非递归部分指定的长度,超出长度的内容会被截断
- 递归子查询里面,递归部分访问非递归部分的字段是通过字段名称,而不是字段所在的位置。比如下面这个 SQL,你觉得会输出什么呢?
WITH RECURSIVE cte AS
(
SELECT 1 AS n, 1 AS p, -1 AS q
UNION ALL
SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 3
)
SELECT * FROM cte;
- 修改递归的最大深度、允许递归语句运行的最长时间。
这些在官方文档里都有详细的说明,想深入研究就去看官方文档吧。
发布于 2020-07-21 07:39