SQL 中的 WITH ... AS ...-平芜编程栈

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	子查询	临时表
可读性	⭐⭐⭐⭐	⭐⭐	⭐⭐⭐
性能	通常与子查询相当（优化器会重写）	同左	可能更快（可加索引）
作用域	仅当前语句	仅当前查询块	当前会话
支持递归	✅	❌	❌（除非手动循环）
可重复引用	✅（多次）	❌（需复制）	✅