首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >SQL 的递归表达式

SQL 的递归表达式

作者头像
白日梦想家
发布2020-07-17 14:07:46
1.2K0
发布2020-07-17 14:07:46
举报
文章被收录于专栏:SQL实现SQL实现

MySQL 在 8.0 的版本引入了公共表表达式(Common Table Expressions),简称 CTE。CTE 在一些方面可以简化我们的 SQL 语句,让它看起来不至于太臃肿。

CTE 还可以用来写递归,我在旧文(SQL 生成斐波那契数列)里说找个时间和大家说下递归的实现, 今天正好有这个时间。

先来看一个 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

递归表达式的语句有什么特点呢?

  1. 使用 WITH RECURSIVE 开头,关键词 RECURSIVE 表明这段表达式是递归表达式;
  2. 自引用。递归的子查询有两部分,使用 [UNION [ALL]] 或 [UNION DISTINCT] 分开。
 SELECT ...      # 返回初始数据集
 UNION ALL
 SELECT ...      # 返回递归后的数据集

第二个 SELECT 里面 FROM 子句之后接的是 CTE 名称,即在这里它引用了自身,这也是实现递归的关键逻辑所在。

要检查一段递归表达式的是否存在问题,需要看这几个方面:

  1. 有初始数据集和边界条件,当达到了边界递归将不再继续;
  2. 正确的迭代表达式。

就拿刚才的 Demo 来说,它的初始数据集是 n = 1 ,终止条件是 n < 5,迭代的表达式是 n = n + 1。如果没有终止条件或者表达式写得有问题(比如把n = n + 1 写成 n = n - 1),SQL 直到超出了递归最大深度后才会终止。

递归表达式可以用来做什么呢?

  1. 生成斐波那契数列,可参考旧文;
  2. 补全两个日期之间的缺失日期;
  3. 树形查询。

举一个递归实现树形查询的例子,还是拿 emp 表来说吧。我想知道 emp 表中每个员工的和 boss 之间的层级关系,以及员工所在的层级,使用递归就可以这么做:

  1. 先获取到 boss 的信息;
  2. 然后根据上下级关系不断去迭代,直到找到所有没有下级的员工的信息。
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  

这篇文章没有涉及到或者讲得不深但我觉得又比较重要的地方:

  1. 字符串类型的字段的长度在非递归部分指定。因此,在递归子查询中,如果某个字段(字符串类型),在递归部分的长度超过了非递归部分指定的长度,超出长度的内容会被截断
  2. 在递归子查询里面,递归部分访问非递归部分的字段是通过字段名称,而不是字段所在的位置。比如下面这个 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;
  1. 修改递归的最大深度、允许递归语句运行的最长时间。

这些在官方文档里都有详细的说明,想深入研究就去看官方文档吧。

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2020-07-09,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 SQL实现 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL(TencentDB for MySQL)为用户提供安全可靠,性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性,包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL,可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理,不仅经济实惠,而且稳定可靠,易于运维。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档