SQL语法进阶篇(三)，数据库复杂查询——递归函数

三、递归查询

1. 界说

递归查询是一种通过自身调用自身来渐渐构建查询结果的方法。用于处置惩罚具有条理结构的数据，如树形结构（组织结构图、文件体系目录）或图结构。在 SQL 中，通常使用 WITH RECURSIVE关键字来实现。

• 语法结构：WITH RECURSIVE + 初始查询（锚成员） + 递归查询（递归成员）
  

递归查询的实行过程

• 初始查询：首先实行初始查询，得到一个初始结果集。
  
• 递归查询：将初始结果集作为输入，实行递归查询，将递归查询的结果与初始结果集合并。
  
• 终止条件：重复实行递归查询，直到没有新的行可以添加到结果集中，即满足终止条件。
  

  2. UNION ALL 在递归查询中的核心作用

递归查询由两部门组成：

• 锚成员（Initial Query）：天生递归的初始数据（出发点），例如 “顶级员工”“根节点” 等。
  
• 递归成员（Recursive Query）：通过引用自身（CTE 名称），基于上一次的结果集天生新数据（如下属员工、子节点等）。
  

UNION ALL 的作用：

• 合并结果集：将锚成员的结果与递归成员每次迭代的结果按行合并，形成最终的递归结果集。
  
• 保留全部行，包括重复行：UNION ALL 不会去重，会保留全部行。在递归中，每一层递归天生的新行都是差别层级的数据（如员工的直接下属、下属的下属等），这些行必要全部保留，不能去重。
  

3. UNION ALL vs UNION：关键区别

特性UNION ALLUNION去重不实行去重，保留全部行自动去重，合并后去除重复行性能更高（无需去重处置惩罚）较低（需对结果集举行去重排序）递归中的适用性必须使用（递归天生的行需全部保留）一般不使用（大概导致数据丢失） 为什么递归中不能用 UNION？

• 递归天生的行是层级扩展：例如，员工 A 的下属 B，B 的下属 C，每一层都是新行，不存在重复，UNION 去重会错误地删除层级数据。
  
• 语法限定：部门数据库（如 PostgreSQL、MySQL）在递归 CTE 中强制要求使用 UNION ALL，使用 UNION 会报错。
  

4. UNION ALL 的必要性：制止数据丢失

假设错误地使用 UNION，会导致以下问题：

• 去重误删层级数据：若某次递归天生的行与之前某层的行完全雷同（虽然在递归中这种环境罕见，但结构上允许），UNION 会删除重复行，导致层级缺失。
  
• 语法错误：部门数据库（如 PostgreSQL）在递归 CTE 中要求必须使用 UNION ALL，使用 UNION 会报错：
  1. ERROR:  recursive CTE "employee_hierarchy" must use UNION ALL to combine recursive parts

  复制代码

5. 性能优化：选择 UNION ALL 的原因

• 无需排序去重：UNION ALL 直接合并结果集，省去了 UNION 的去重排序步骤，在递归层数较多时性能优势明显。
  
• 数据量可控：递归查询的结果集通常是层级扩展，天然无重复行，UNION ALL 能更高效地处置惩罚数据。
  

6. 实战示例及表明

示例1： 查询员工及其全部下属
假设 employee 表记录了公司的组织结构，包含 employee_id（员工 ID）、employee_name（员工姓名）和 manager_id（上级 ID）三个字段，用于体现员工之间的层级关系：

• 在举行递归查询操作之前，我先使用 Kooboo 中的sql数据库在线工具，创建employee表并插入数据 -> 实行操作
  
  
  

---

1. -- 递归查询员工及其所有下属
2. WITH RECURSIVE employee_hierarchy AS (
3.     -- 初始查询（锚成员）：找到顶级员工
4.     SELECT
5.         employee_id,
6.         employee_name,
7.         manager_id
8.     FROM
9.         employees
10.     WHERE
11.         manager_id IS NULL
12.     UNION ALL
13.     -- 递归查询（递归成员）：查找下属员工
14.     SELECT
15.         e.employee_id,
16.         e.employee_name,
17.         e.manager_id
18.     FROM
19.         employees e
20.     JOIN
21.         employee_hierarchy eh ON e.manager_id = eh.employee_id
22. )
23. SELECT * FROM employee_hierarchy;

复制代码

表明：

• 初始查询（锚成员）：查找 manager_id 为 NULL 的员工，即顶级员工。
  
• 递归查询（递归成员）：通过 JOIN 操作，将 employees 表与 employee_hierarchy CTE 连接，查找每个员工的下属。
  
• 主查询：从 employee_hierarchy CTE 中获取最闭幕果，包含全部员工及其层级关系。
  

示例 2：天生连续的数字序列

1. -- 递归查询生成数字序列
2. WITH RECURSIVE numbers AS (
3.     -- 初始查询（锚成员）：生成第一个数字 1
4.     SELECT 1 AS num
5.     UNION ALL
6.     -- 递归查询（递归成员）：生成下一个数字
7.     SELECT num + 1
8.     FROM numbers
9.     WHERE num < 10
10. )
11. SELECT * FROM numbers;

复制代码

表明：

• 初始查询（锚成员）：天生数字 1。
  
• 递归查询（递归成员）：每次将上一次的数字加 1，直到数字达到 10。
  
• 主查询：从 numbers CTE 中获取最终的数字序列。
  

注意事项

• 终止条件：递归查询必须有明白的终止条件，否则会导致无限递归，耗尽体系资源。在递归查询中，通常使用 WHERE 子句来设置终止条件。
  
• 性能问题：递归查询大概会导致性能问题，尤其是在处置惩罚大规模数据时。可以通过优化查询语句、添加得当的索引等方式来提高性能。
  
• 数据库兼容性：差别数据库体系对递归查询的支持大概略有差别。例如，某些数据库大概必要使用差别的关键字或语法来实现递归查询。在使用递归查询时，必要参考相应数据库的文档。
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。