在MySQL中，公共表表达式（Common Table Expressions，简称CTE）是一种临时的结果集，可以在单个查询中被引用多次。CTE通常用于递归查询和复杂的数据操作。MySQL 8.0及以上版本支持CTE。
以下是使用CTE的基本语法：
基本CTE写法
WITH cte_name (column1, column2, ...)
AS
(
-- CTE的定义部分，可以是任意的SELECT语句
SELECT column1, column2, ...
FROM table_name
WHERE condition
)
SELECT *
FROM cte_name
WHERE another_condition;
递归CTE写法
递归CTE是CTE的一种特殊形式，它允许查询自身引用。这在处理层次或递归数据结构时非常有用。
WITH RECURSIVE cte_name (column1, column2, ...)
AS
(
-- 递归的初始部分，定义了递归的起点
SELECT column1, column2, ...
FROM table_name
WHERE condition
UNION ALL
-- 递归部分，递归地引用CTE本身
SELECT column1, column2, ...
FROM cte_name
JOIN table_name ON join_condition
WHERE another_condition
)
SELECT *
FROM cte_name
WHERE yet_another_condition;
示例
假设我们有一个员工表employees，包含员工ID、员工名字和经理ID，我们想要查询所有员工及其下属的名单。
WITH RECURSIVE employee_cte (employee_id, employee_name, manager_id, level) AS
(
-- 基础查询，选择所有员工作为起点
SELECT employee_id, employee_name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL -- 假设NULL表示顶级经理
UNION ALL
-- 递归查询，连接员工表和CTE
SELECT e.employee_id, e.employee_name, e.manager_id, ec.level + 1
FROM employees e
INNER JOIN employee_cte ec ON ec.employee_id = e.manager_id
)
SELECT *
FROM employee_cte;
在这个例子中，我们首先选择了所有没有经理的员工（即顶级经理），然后递归地选择了所有下属员工。
注意事项
- CTE必须在查询的最上方定义。
- CTE可以在SELECT、INSERT、UPDATE或DELETE语句中使用。
- 递归CTE使用
WITH RECURSIVE关键字，并且必须包含初始查询（称为锚点）和递归查询。 - 在递归CTE中，
UNION ALL是必须的，它将基础查询和递归查询的结果合并起来。 - 递归查询可能会无限递归，因此通常需要一个WHERE子句来限制递归的深度。
CTE提供了一种强大的方式来编写更清晰、更易于理解的复杂查询。在MySQL 8.0及以上版本中，CTE是处理层次数据和递归查询的推荐方法。
