SQL中WITH RECURSIVE的用法

SQL中WITH RECURSIVE的用法

定义

WITH RECURSIVE 是 SQL 中的一种高级查询结构，用于执行递归查询。递归查询是一种特殊的查询方式，它能够通过反复应用一个规则或算法，逐步构建出一个结果集，常用于解决层次化或树状数据结构的遍历问题。

WITH RECURSIVE 结构通常包含以下几个关键部分：

1. CTE（Common Table Expression，公用表表达式）：

• WITH 关键字引入一个或多个 CTE。
• ECURSIVE 关键字表明至少有一个 CTE 是递归的。
• CTE 名称（cte_name）用于标识递归查询的临时结果集。
• 列名列表（column_list）定义了 CTE 结果集中包含的列及其名称。
• 初始查询（initial_query_result）提供递归过程的起点，即第一次迭代时使用的数据。
• 递归部分（递归子查询）定义了如何将前一次迭代的结果作为输入，计算出下一次迭代的数据。

2.递归查询的结构

• 递归查询通常由两部分构成：初始化查询（非递归部分）和递归子查询（递归部分）。
• 初始化查询：定义递归开始时的基础数据集，通常是与递归逻辑相关的最顶层数据或边界条件。
• 递归子查询：定义如何根据前一次迭代的结果生成下一次迭代的数据。递归子查询通常包含对自身 CTE 名称的引用，以递归地应用相同的操作。

3.连接操作符：

• 递归查询的初始化查询和递归子查询通常通过 UNION 或 UNION ALL 连接起来，形成一个完整的递归查询表达式。
• UNION 会去除结果集中的重复行，而 UNION ALL 不会去除重复，根据实际需求选择合适的连接操作符。

4.终止条件

• 递归查询必须有一个明确的终止条件，否则会无限循环下去。终止条件通常隐含在递归子查询的 WHERE 子句或其他逻辑中，当满足特定条件时，不再产生新的结果。

示例

假设有一个员工表 employees，其中包含 id（员工ID）、name（员工姓名）、manager_id（上级经理ID），结构如下：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);
1
2
3
4
5
6

现在我们想查询出一个员工及其所有下属的完整层级关系。可以使用 WITH RECURSIVE 构建如下查询：

WITH RECURSIVE employee_hierarchy AS (
    -- 初始化查询：选取根节点（顶级经理，没有上级经理）
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 递归子查询：根据上一层级结果，查找下一级员工
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

在这个例子中：

• employee_hierarchy 是 CTE 的名称。
• 列名列表为 id, name, manager_id, level。
• 初始化查询选择了所有没有上级经理（manager_id IS NULL）的员工作为递归的起点。
• 递归子查询通过 JOIN 语句将 employee_hierarchy 与 employees 表连接起来，根据 manager_id 匹配关系找到下一层级的员工。同时，level 列每次递归时增加 1，表示员工在层级结构中的深度。
• 最后，通过 SELECT * FROM employee_hierarchy 查询出最终的递归结果集。

总结起来，WITH RECURSIVE 的用法主要包括以下几个步骤：

1. 定义 CTE，指定 RECURSIVE 关键字。
2. 编写初始化查询和递归子查询，通过 UNION 或 UNION ALL 连接。
3. 确保递归子查询中包含终止条件，以避免无限递归。
4. 在主查询中引用 CTE 名称，获取递归查询的结果。

这种结构适用于解决各种层次化数据的遍历问题，如组织架构、目录结构、路径搜索、数列生成等。通过递归查询，可以简化复杂查询逻辑，提高代码可读性，并且在某些情况下比使用循环或其他编程语言结构更高效。