编辑
2024-07-20
未分类
00
请注意,本文编写于 122 天前,最后修改于 49 天前,其中某些信息可能已经过时。

目录

表结构
递归 SQL
执行过程
1. 初始成员
2. 递归成员
3. 最终结果
问题
必须要使用 union all 吗?

说是递归,该执行过程更像是广度优先遍历!

表结构

sql
CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), manager_id INT ); INSERT INTO employees (id, name, manager_id) VALUES (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 1), (4, 'David', 2), (5, 'Eve', 2), (6, 'Frank', 3);

递归 SQL

找出所有员工及其上级

sql
WITH RECURSIVE org_structure AS ( -- 初始成员:根节点(没有上级的员工) SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL UNION ALL -- 递归成员:当前节点的所有下级 SELECT e.id, e.name, e.manager_id FROM employees e INNER JOIN org_structure os ON os.id = e.manager_id ) SELECT * FROM org_structure;

执行过程

递归 CTE 的执行过程分为两个主要部分:初始成员和递归成员。

1. 初始成员

首先,执行 CTE 的初始成员部分:

sql
SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL

这将选择所有没有上级的员工。在我们的示例中,只有 Alice 没有上级:

sql
id | name | manager_id ---|-------|------------ 1 | Alice | NULL

2. 递归成员

接下来,执行递归成员部分,将初始成员作为输入:

sql
SELECT e.id, e.name, e.manager_id FROM employees e INNER JOIN org_structure os ON os.id = e.manager_id

第一次递归:使用初始成员(Alice)的结果。查找所有上级为 Alice 的员工:

sql
id | name | manager_id ---|---------|------------ 2 | Bob | 1 3 | Charlie | 1

第二次递归:使用第一次递归的结果(Bob 和 Charlie)。查找所有上级为 Bob 和 Charlie 的员工:

sql
id | name | manager_id ---|-------|------------ 4 | David | 2 5 | Eve | 2 6 | Frank | 3

第三次递归:使用第二次递归的结果(David、Eve 和 Frank)。查找所有上级为 David、Eve 和 Frank 的员工。这次没有更多员工,因此递归停止。

3. 最终结果

将初始成员和所有递归成员的结果合并,得到最终的组织结构:

sql
id | name | manager_id ---|---------|------------ 1 | Alice | NULL 2 | Bob | 1 3 | Charlie | 1 4 | David | 2 5 | Eve | 2 6 | Frank | 3

问题

必须要使用 union all 吗?

是的:

  • 递归 CTE 通过不断地将结果集与自己连接来构建最终结果。如果使用 UNION,重复的行会被自动去除,这可能会导致丢失一些必要的递归步骤,进而导致不完整或不正确的结果。
  • UNION 会对结果集进行去重操作,这不仅增加了额外的计算开销,而且在递归查询中可能会频繁触发这种去重操作,从而导致性能问题。

本文作者:jdxj

本文链接:

版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!