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 ALL	UNION
去重	不执行去重，保留所有行	自动去重，合并后去除重复行
性能	更高（无需去重处理）	较低（需对结果集进行去重排序）
递归中的适用性	必须使用（递归生成的行需全部保留）	一般不使用（可能导致数据丢失）

为什么递归中不能用 UNION？

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

4. UNION ALL 的必要性：避免数据丢失

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

1. 去重误删层级数据：若某次递归生成的行与之前某层的行完全相同（虽然在递归中这种情况罕见，但结构上允许），UNION 会删除重复行，导致层级缺失。
2. 语法错误：部分数据库（如 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 中获取最终的数字序列。

注意事项

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