【MySQL】MySQL版本8+ 的 with recursive 两种递归语法的使用

发布时间:2024年01月09日

力扣题

1、题目地址

1270. 向公司 CEO 汇报工作的所有人

2、模拟表

员工表:Employees

Column NameType
employee_idint
employee_namevarchar
manager_idint
  • employee_id 是这个表具有唯一值的列。
  • 这个表中每一行中,employee_id 表示职工的 ID,employee_name 表示职工的名字,manager_id 表示该职工汇报工作的直线经理。
  • 这个公司 CEO 是 employee_id = 1 的人。

3、要求

编写解决方案,找出所有直接或间接向公司 CEO 汇报工作的职工的 employee_id 。

由于公司规模较小,经理之间的间接关系 不超过 3 个经理 。

可以以 任何顺序 返回无重复项的结果。

返回结果示例如下。

示例 1:

输入:
Employees 表:

employee_idemployee_namemanager_id
1Boss1
3Alice3
2Bob1
4Daniel2
7Luis4
8Jhon3
9Angela8
77Robert1

输出:

employee_id
2
77
4
7

解释:
公司 CEO 的 employee_id 是 1.
employee_id 是 2 和 77 的职员直接汇报给公司 CEO。
employee_id 是 4 的职员间接汇报给公司 CEO 4 --> 2 --> 1 。
employee_id 是 7 的职员间接汇报给公司 CEO 7 --> 4 --> 2 --> 1 。
employee_id 是 3, 8 ,9 的职员不会直接或间接的汇报给公司 CEO。

4、代码编写

我的写法

代码
SELECT employee_id
FROM Employees
WHERE manager_id = 1 AND employee_id != 1
UNION ALL
SELECT employee_id
FROM Employees
WHERE manager_id IN (
    SELECT employee_id
    FROM Employees
    WHERE manager_id = 1 AND employee_id != 1
)
UNION ALL
SELECT employee_id
FROM Employees
WHERE manager_id IN (
    SELECT employee_id
    FROM Employees
    WHERE manager_id IN (
        SELECT employee_id
        FROM Employees
        WHERE manager_id = 1 AND employee_id != 1
    )
)
代码分析

要求里面有提到:由于公司规模较小,经理之间的间接关系 不超过 3 个经理 ,所以分为三个即可。

直接连接经理:(第一个)

SELECT employee_id
FROM Employees
WHERE manager_id = 1 AND employee_id != 1

间接连接经理:(UNION ALL 连接重复不去重,第二个和第三个)

SELECT employee_id
FROM Employees
WHERE manager_id IN (
    SELECT employee_id
    FROM Employees
    WHERE manager_id = 1 AND employee_id != 1
)
UNION ALL
SELECT employee_id
FROM Employees
WHERE manager_id IN (
    SELECT employee_id
    FROM Employees
    WHERE manager_id IN (
        SELECT employee_id
        FROM Employees
        WHERE manager_id = 1 AND employee_id != 1
    )
)

网友给出的连表写法(适用于关系层次不深的情景)

代码
SELECT DISTINCT e1.employee_id
FROM Employees e1, Employees e2, Employees e3
WHERE e1.manager_id = e2.employee_id 
AND e2.manager_id = e3.employee_id
AND e3.manager_id = 1 
AND e1.employee_id != 1
代码分析

1、e1.employee_id != 1 这个是排除掉经理(3、2、4、7、8、9、77)
2、e3.manager_id = 1 这个是找出直接连接经理的员工(2,77)
3、e2.manager_id = e3.employee_id 这个是找出直接连接经理的员工的下级员工(2 - 4,77)
4、e1.manager_id = e2.employee_id 这个是找出上面第 3 点下级员工的下级员工(2 - 4 - 7,77)

要从连表去分析太乱了,上面已经是我暂时能理解到的了。
有比较好的理解的小伙伴可以在评论区发表一下自己的见解。

递归写法

with recursive 递归语法知识
1、在 with… as 中声明需要递归的字段

语法:

WITH RECURSIVE 表名 (n) AS
( 
	select 初始值 from table
	union all
	select 递归内容 from 表名 where (终止条件)
)
[ SELECT | INSERT | UPDATE | DELETE ]

例子:

WITH RECURSIVE cte (n) AS  
(
	SELECT 1  # 初始值
	UNION ALL
	SELECT n + 1 FROM cte WHERE n < 5  # 递归内容 from cte 表
)
SELECT * FROM cte;

输出结果:

+-----+
|  n  |
+-----+
|  1  |
|  2  |
|  3  |
|  4  |
|  5  |
+-----+

如上所示,可以看出上面 SELECT 1 就代表了输出结果 第一行的数据,也决定了 n 的初始值就是 1,下面 SELECT n + 1 FROM cte WHERE n < 5 是根据上面标注 cte(n)n 为递归字段,重复查询时只要 n < 5 就不断执行 n + 1 操作,当 n = 4 时,n 满足小于 5 的条件,查询出 n + 1 = 5,输出 5n 也变成 5,这时不满足 n < 5 的条件就查询结束了。

2、在 sql 语句中第一段的初始值内声明变量

语法:

WITH recursive 表名 AS ( 
	初始语句(非递归部分) 
	UNION ALL 
	递归部分语句
)
[ SELECT | INSERT | UPDATE | DELETE ]

例子:

WITH RECURSIVE cte AS
(
	SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
	UNION ALL
	SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

输出结果:

+-----+--------------+
|  n  | str          |
+-----+--------------+
|  1  | abc          |
|  2  | abcabc       |
|  3  | abcabcabcabc |
+-----+--------------+

如上所示,可以看出 SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str 代表了输出结果 第一行的数据,也决定了 n 的初始值是 1str 的初始值是 abc,两个都为递归字段,和上面一样,只要满足下面递归语句的条件 n < 3,就累积操作,n + 1 就是 123CONCAT(str, str) 就是 abcabcabcabcabcabcabc

3、参考

SQL:With recursive 递归用法
MySQL8 使用 with recursive 实现递归

代码
WITH RECURSIVE cte AS
(
    SELECT employee_id 
    FROM Employees a 
    WHERE employee_id != 1 AND manager_id = 1
    UNION ALL
    SELECT a.employee_id 
    FROM Employees AS a 
    JOIN cte AS b ON a.manager_id = b.employee_id  
)
SELECT * FROM cte;
代码分析

1、对 employee_id 进行初始化操作,赋予初始值,也为递归字段(查询到直接连接经理的员工)(2,77)

SELECT employee_id 
FROM Employees
WHERE employee_id != 1 AND manager_id = 1

2、开始时上面初始化操作后直接 employee_id 赋值到 b.employee_id,再查询出 a.employee_id 赋值到 employee_id(其实来说 employee_id 就是 b.employee_id,我们可以看到上面 sqlWITH RECURSIVE cte AS 用了 cte,初始值用了 employee_id 作为递归字段,下面递归语句里面 cte 别名为 b,那肯定 b.employee_id 就是 employee_id 了,如果有说错可以标注或评论指出),这里就相当于是查询出直接连接经理员工下面的间接员工(2 - 4,77)(2 - 4 - 7,77)

SELECT a.employee_id 
FROM Employees AS a 
JOIN cte AS b ON a.manager_id = b.employee_id

3、第一次:2,77,第二次:2 - 4,77,第三次:2 - 4 - 7,77

SELECT * FROM cte;

4、会发现确实是 2 和 77 直接连接经理先输出,之后再输出间接连接经理的

| employee_id |
| ----------- |
| 2           |
| 77          |
| 4           |
| 7           |
文章来源:https://blog.csdn.net/weixin_50223520/article/details/135471130
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。