SQL WITH 子句：公用表表达式（CTE）和递归查询
Published: 2026-02-04
WITH 子句（也称为公用表表达式，Common Table Expression，简称 CTE）允许你在查询中定义临时的具名结果集，并在后续语句中引用它们。这能让复杂查询更易读、更易复用。大多数现代 SQL 数据库都支持它，包括 SQLite、PostgreSQL、MySQL 8.0+、SQL Server 和 Oracle。
示例数据
下面所有示例都使用两张表：
employees 表：
| id | name | manager_id | salary |
|---|---|---|---|
| 1 | Alice | NULL | 120000 |
| 2 | Bob | 1 | 80000 |
| 3 | Carol | 1 | 85000 |
| 4 | Dave | 2 | 60000 |
| 5 | Eve | 2 | 62000 |
| 6 | Frank | 3 | 58000 |
sales 表：
| id | employee_id | amount | month |
|---|---|---|---|
| 1 | 2 | 5000 | January |
| 2 | 2 | 6000 | February |
| 3 | 3 | 7000 | January |
| 4 | 3 | 8000 | February |
| 5 | 4 | 3000 | January |
| 6 | 5 | 4000 | January |
| 7 | 6 | 2500 | February |
基本 WITH 子句（非递归）
WITH 子句定义一个或多个具名的临时表，这些表只在当前查询执行期间存在。
示例：按员工统计总销售额
employee_sales CTE 计算每个员工的总销售额，然后我们将它与 employees 表连接以显示姓名。
多个 CTE
你可以定义多个用逗号分隔的 CTE。后面的 CTE 可以引用前面的 CTE。
示例：包含多种指标的销售分析
WITH RECURSIVE：遍历层级结构
WITH RECURSIVE 允许 CTE 引用自身，从而遍历层级或树形数据。这对组织结构图、分类层级或任何父子关系都非常有用。
一个递归 CTE 包含两部分：
- 基础查询（Base query）：初始行集合
- 递归查询（Recursive query）：引用 CTE 本身以查找相关行
示例：组织结构图（某个经理下的所有员工）
查找 Alice（CEO）下的所有员工：
示例：管理链（查找上级层级）
查找 Dave 的完整管理链（Dave 汇报给 Bob，Bob 汇报给 Alice）：
各数据库中的 WITH RECURSIVE 语法
WITH RECURSIVE 在以下数据库中受支持：
- SQLite — 3.8.3+（2013）
- PostgreSQL — 8.4+（2009）
- SQL Server — 2005+（称为 “Recursive CTE”）
- Oracle — 11g+（称为 “Recursive Subquery Factoring”）
- MySQL — 8.0+（2018）
注意： 较早版本的 MySQL 以及某些其他数据库不支持递归 CTE——它们可能需要使用存储过程或在应用层实现递归等其他方式。
WITH 子句的常见用法
- 简化复杂查询 —— 将大型查询拆分为多个逻辑步骤
- 复用子查询 —— 某个计算只定义一次，多处使用
- 层级遍历 —— 遍历树形结构（组织架构、分类、评论）
- 生成序列 —— 创建数字范围或日期范围
- 渐进聚合 —— 分步骤构建聚合结果
小结
WITH（CTE）为查询创建可复用的具名临时结果集- 可以将多个 CTE 串联在一起使用
WITH RECURSIVE允许 CTE 自引用，用于层级查询- 递归 CTE 需要一个基础部分和一个递归部分，并用
UNION ALL连接 - 所有现代 SQL 数据库都支持（语法可能略有差异）
可以尝试修改上面的示例，探索不同的层级结构或计算方式！