SQL 中的WITH ... AS ...是一种非常强大且常用的语法结构，用于定义公用表表达式（Common Table Expression，简称CTE）。它可以帮助你将复杂的查询拆解为更清晰、可读性更强的逻辑块。
一、基本语法
WITH cte_name AS ( -- 子查询（SELECT 语句） SELECT ... ) SELECT * FROM cte_name;cte_name：你给这个临时结果集起的名字（类似一个临时视图）。AS (...)：括号内是一个标准的SELECT查询，不能包含INSERT、UPDATE、DELETE等 DML 语句。- CTE 只在当前语句中有效，执行完就释放，不会持久化。
二、为什么用 CTE？
- 提高可读性：把复杂逻辑分解成多个步骤。
- 避免重复子查询：如果同一个子查询要用多次，用 CTE 定义一次即可。
- 支持递归查询：这是 CTE 最强大的特性之一（如查询组织架构、树形结构等）。
三、简单示例
假设有一张员工表employees(id, name, manager_id, salary)：
示例 1：非递归 CTE
找出工资高于平均工资的员工：
WITH avg_salary AS ( SELECT AVG(salary) AS avg_sal FROM employees ) SELECT e.name, e.salary FROM employees e JOIN avg_salary a ON e.salary > a.avg_sal;这里
avg_salary是一个只含一行一列的临时表，供主查询使用。
示例 2：多个 CTE（用逗号分隔）
WITH high_earners AS ( SELECT id, name, salary FROM employees WHERE salary > 100000 ), dept_counts AS ( SELECT department_id, COUNT(*) AS emp_count FROM employees GROUP BY department_id ) SELECT h.name, h.salary, d.emp_count FROM high_earners h JOIN employees e ON h.id = e.id JOIN dept_counts d ON e.department_id = d.department_id;多个 CTE 之间用逗号分隔，最后一个 CTE 后直接跟主查询。
四、递归 CTE（Recursive CTE）
用于处理层级结构或树形数据，比如：公司组织架构、评论的父子关系、物料清单等。
递归 CTE 语法：
WITH RECURSIVE cte_name AS ( -- 初始查询（锚点成员） SELECT ... UNION ALL -- 递归查询（引用自身） SELECT ... FROM cte_name JOIN ... ) SELECT * FROM cte_name;注意：有些数据库（如 PostgreSQL、MySQL 8.0+、SQL Server）需要写
WITH RECURSIVE，而有些（如 SQL Server）只需WITH即可自动识别递归。
示例：查询某员工的所有下属（多级）
WITH RECURSIVE subordinates AS ( -- 锚点：从某个经理开始（比如 id = 1） SELECT id, name, manager_id, 0 AS level FROM employees WHERE id = 1 UNION ALL -- 递归：找下一级员工 SELECT e.id, e.name, e.manager_id, s.level + 1 FROM employees e INNER JOIN subordinates s ON e.manager_id = s.id ) SELECT * FROM subordinates;这会返回 ID 为 1 的员工及其所有直接/间接下属，并标注层级深度。
五、注意事项
| 数据库 | 是否支持 CTE | 是否需要RECURSIVE关键字 |
|---|---|---|
| PostgreSQL | ✅ | 需要（递归时） |
| MySQL 8.0+ | ✅ | 需要（递归时） |
| SQL Server | ✅ | 不需要（自动识别） |
| Oracle | ✅ | 不需要（但可用） |
| SQLite 3.8.3+ | ✅ | 需要（递归时） |
| 旧版 MySQL（<8.0） | ❌ | 不支持 |
六、CTE vs 子查询 vs 临时表
| 特性 | CTE | 子查询 | 临时表 |
|---|---|---|---|
| 可读性 | ⭐⭐⭐⭐ | ⭐⭐ | ⭐⭐⭐ |
| 性能 | 通常与子查询相当（优化器会重写） | 同左 | 可能更快（可加索引） |
| 作用域 | 仅当前语句 | 仅当前查询块 | 当前会话 |
| 支持递归 | ✅ | ❌ | ❌（除非手动循环） |
| 可重复引用 | ✅（多次） | ❌（需复制） | ✅ |
七、总结
WITH ... AS ...定义的是临时命名结果集（CTE）。- 适用于简化复杂查询、提升可读性、实现递归逻辑。
- 在现代 SQL 开发中几乎是必备技能。
