在SQL中，递归公用表表达式（Recursive Common Table Expressions, 递归CTE）是一种强大的工具，用于处理层次结构数据或需要递归查询的数据。递归CTE允许你在同一个查询中引用自身，从而实现递归逻辑。
语法
递归CTE的基本语法如下：
WITH RECURSIVE cte_name AS (
-- 初始查询（锚成员）
SELECT ...
FROM ...
UNION ALL
-- 递归查询（递归成员）
SELECT ...
FROM cte_name
JOIN ... ON ...
)
SELECT * FROM cte_name;
介绍
MySQL在8.0.1版本之后开始支持CTEs。这种特性允许用户定义递归查询，在层次或树结构的数据中特别有用。使用WITH RECURSIVE子句开头去定义递归公用表，后面跟着CTE名称和定义。
组成部分
- 初始查询（锚成员）：这是递归的起点，通常是一个普通的SELECT语句，它返回递归过程的初始数据集。
- 递归查询（递归成员）：这部分定义了递归的逻辑。它通过引用CTE本身来生成新的结果集，并与初始查询的结果集合并。
- UNION ALL：将初始查询和递归查询的结果集合并在一起。
示例
假设我们有一个简单的员工表 employees，其中包含员工ID (emp_id) 和他们的经理ID (mgr_id)，我们需要找出所有直接或间接向某个特定经理报告的员工。
表结构
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(100),
mgr_id INT
);
插入一些示例数据
INSERT INTO employees (emp_id, name, mgr_id) VALUES
(1, 'Alice', NULL), -- Alice is the top manager
(2, 'Bob', 1),
(3, 'Charlie', 2),
(4, 'David', 2),
(5, 'Eve', 1),
(6, 'Frank', 5);
递归CTE查询
WITH RECURSIVE employee_hierarchy AS (
-- 初始查询：找到直接向Alice报告的员工
SELECT emp_id, name, mgr_id
FROM employees
WHERE mgr_id = 1 -- 假设我们要找的是Alice (emp_id=1) 的下属
UNION ALL
-- 递归查询：找到这些员工的下属
SELECT e.emp_id, e.name, e.mgr_id
FROM employees e
JOIN employee_hierarchy eh ON e.mgr_id = eh.emp_id
)
-- 最终选择所有的结果
SELECT * FROM employee_hierarchy;
解释
- 初始查询：从
employees表中选择所有直接向Alice报告的员工（即mgr_id为 1 的员工）。 - 递归查询：通过将
employees表与递归CTEemployee_hierarchy进行连接，找到这些员工的所有下属。 - UNION ALL：将初始查询和递归查询的结果集合并在一起。
结果
这个查询将返回所有直接或间接向 Alice 报告的员工列表。
注意事项
- 递归CTE必须是有限的，否则会导致无限循环。数据库系统通常会设置一个默认的最大递归深度（例如，PostgreSQL 默认最大深度为100），但你可以通过配置选项来修改这个限制。
- 在使用递归CTE时，确保你的递归逻辑是正确的，并且不会导致无限循环。
递归CTE在处理层次结构数据（如组织结构、分类树等）时非常有用，可以简化复杂的查询逻辑。
Citations
[1] dev.mysql.com/blog-archiv…
