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

力扣题

1、题目地址

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

2、模拟表

员工表：Employees

Column Name	Type
employee_id	int
employee_name	varchar
manager_id	int

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

3、要求

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

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

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

返回结果示例如下。

示例 1：

输入：
Employees 表：

employee_id	employee_name	manager_id
1	Boss	1
3	Alice	3
2	Bob	1
4	Daniel	2
7	Luis	4
8	Jhon	3
9	Angela	8
77	Robert	1

输出：

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，输出 5，n 也变成 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 的初始值是 1，str 的初始值是 abc，两个都为递归字段，和上面一样，只要满足下面递归语句的条件 n < 3，就累积操作，n + 1 就是 1 → 2 → 3，CONCAT(str, str) 就是 abc → abcabc → abcabcabcabc

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，我们可以看到上面 sql，WITH 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           |