三、递归查询
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 会报错： 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表并插入数据 -> 执行操作
-- 递归查询员工及其所有下属
WITH RECURSIVE employee_hierarchy AS (
-- 初始查询（锚成员）：找到顶级员工
SELECT
employee_id,
employee_name,
manager_id
FROM
employees
WHERE
manager_id IS NULL
UNION ALL
-- 递归查询（递归成员）：查找下属员工
SELECT
e.employee_id,
e.employee_name,
e.manager_id
FROM
employees e
JOIN
employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;
解释：
初始查询（锚成员）：查找 manager_id 为 NULL 的员工，即顶级员工。递归查询（递归成员）：通过 JOIN 操作，将 employees 表与 employee_hierarchy CTE 连接，查找每个员工的下属。主查询：从 employee_hierarchy CTE 中获取最终结果，包含所有员工及其层级关系。
示例 2：生成连续的数字序列
-- 递归查询生成数字序列
WITH RECURSIVE numbers AS (
-- 初始查询（锚成员）：生成第一个数字 1
SELECT 1 AS num
UNION ALL
-- 递归查询（递归成员）：生成下一个数字
SELECT num + 1
FROM numbers
WHERE num < 10
)
SELECT * FROM numbers;
解释：
初始查询（锚成员）：生成数字 1。递归查询（递归成员）：每次将上一次的数字加 1，直到数字达到 10。主查询：从 numbers CTE 中获取最终的数字序列。
注意事项
终止条件：递归查询必须有明确的终止条件，否则会导致无限递归，耗尽系统资源。在递归查询中，通常使用 WHERE 子句来设置终止条件。性能问题：递归查询可能会导致性能问题，尤其是在处理大规模数据时。可以通过优化查询语句、添加适当的索引等方式来提高性能。数据库兼容性：不同数据库系统对递归查询的支持可能略有不同。例如，某些数据库可能需要使用不同的关键字或语法来实现递归查询。在使用递归查询时，需要参考相应数据库的文档。