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)
);
现在我们想查询出一个员工及其所有下属的完整层级关系。可以使用 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;
在这个例子中:
- 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 的用法主要包括以下几个步骤:
- 定义 CTE,指定 RECURSIVE 关键字。
- 编写初始化查询和递归子查询,通过 UNION 或 UNION ALL 连接。
- 确保递归子查询中包含终止条件,以避免无限递归。
- 在主查询中引用 CTE 名称,获取递归查询的结果。
这种结构适用于解决各种层次化数据的遍历问题,如组织架构、目录结构、路径搜索、数列生成等。通过递归查询,可以简化复杂查询逻辑,提高代码可读性,并且在某些情况下比使用循环或其他编程语言结构更高效。