在 MySQL 8 及以上版本中，WITH 子句用于定义公共表表达式（Common Table Expression, CTE），这是一种临时的结果集，可以在后续的查询中引用。CTE 常用于简化复杂查询、增强可读性以及实现递归查询。
CTE 的基本语法
sql
WITH cte_name AS (
-- 这里是查询的主体部分，定义临时结果集
SELECT column1, column2
FROM table
WHERE condition
)
-- 使用 CTE 结果集
SELECT column1, column2
FROM cte_name;
示例 1：基本的 CTE 示例
假设有一个 employees 表，包含 id、name 和 salary，我们想计算工资高于 5000 的员工并返回他们的 id 和 name。
sql
WITH high_salary_employees AS (
SELECT id, name
FROM employees
WHERE salary > 5000
)
SELECT id, name
FROM high_salary_employees;
在这个例子中，high_salary_employees 是 CTE 名称，它保存了所有工资高于 5000 的员工信息。我们可以像查询常规表一样使用它。
示例 2：多 CTE 查询
你可以在 WITH 子句中定义多个 CTE，并通过逗号将它们分开：
sql
WITH
cte1 AS (
SELECT id, name, salary
FROM employees
WHERE salary > 5000
),
cte2 AS (
SELECT id, name
FROM employees
WHERE salary <= 5000
)
-- 使用多个 CTE
SELECT id, name, salary
FROM cte1
UNION
SELECT id, name, NULL AS salary
FROM cte2;
在这个示例中，cte1 包含工资大于 5000 的员工，cte2 包含工资不大于 5000 的员工。最终查询合并了两个 CTE 的结果。
示例 3：递归 CTE（处理树形结构）
递归 CTE 是 MySQL 8 引入的重要功能，通常用于处理层级结构的数据，例如组织结构、目录树等。递归 CTE 由两个部分组成：基础查询部分和递归查询部分。
假设有一个组织结构表 employees，记录了员工的 id、name 和他们的 manager_id，其中 manager_id 是他们上级员工的 id。
sql
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT
);
插入一些数据：
sql
INSERT INTO employees (id, name, manager_id) VALUES
(1, 'Alice', NULL), -- Alice 是 CEO，没有上级
(2, 'Bob', 1), -- Bob 由 Alice 管理
(3, 'Charlie', 1), -- Charlie 由 Alice 管理
(4, 'David', 2), -- David 由 Bob 管理
(5, 'Eve', 2), -- Eve 由 Bob 管理
(6, 'Frank', 3); -- Frank 由 Charlie 管理
你可以使用递归 CTE 查询所有员工及其下属。例如，查询 Alice 及其所有下属：
sql
WITH RECURSIVE employee_hierarchy AS (
-- 基础查询：选择 Alice 作为根节点
SELECT id, name, manager_id
FROM employees
WHERE id = 1 -- Alice 的 ID
UNION ALL
-- 递归查询：选择 Alice 的直接下属
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
-- 查询 Alice 及其所有下属
SELECT id, name, manager_id
FROM employee_hierarchy
ORDER BY id;
解释：
基础查询：选择 id = 1 的员工（即 Alice）作为根节点。
递归查询：通过 JOIN 连接 employees 表和 employee_hierarchy CTE，查询 Alice 的直接下属，并且不断递归直到找到所有下属。
最终结果会显示从 Alice 开始的所有下属，包括直接和间接下属。
查询结果：
plaintext
+----+---------+------------+
| id | name | manager_id |
+----+---------+------------+
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Charlie | 1 |
| 4 | David | 2 |
| 5 | Eve | 2 |
| 6 | Frank | 3 |
+----+---------+------------+
CTE 的优势
提高可读性：将复杂的查询分解为多个部分，每部分的目的更加清晰。
避免子查询重复：当一个查询结果需要在多个地方使用时，CTE 可以避免重复编写相同的子查询。
递归查询支持：MySQL 8 引入的递归 CTE 让处理层级数据（如组织结构、文件夹结构等）变得更加方便。
总结
基础 CTE：用来简化复杂查询，增强查询的可读性。
递归 CTE：用于处理树形结构的数据，支持自引用查询。
在 MySQL 8 中，CTE 是一个非常强大的工具，适用于很多复杂的查询场景。
