一、WITH 子句基础语法结构WITH cte_name [(column_name1, column_name2, ...)] AS ( -- 定义CTE的查询逻辑 SELECT ...)[, cte_name2 AS (...)] -- 可定义多个CTE，用逗号分隔-- 主查询（引用CTE）SELECT ... FROM cte_name ...组件(元素)说明WITH：声明 CTE 块开始的关键字。cte_name：CTE 的临时名称，作用域为紧随其后的主查询。(column_name1, ...)（可选）：显式定义 CTE 的列别名，覆盖查询结果的原始列名。AS (...)：包裹定义 CTE 的 SELECT 语句。多个CTE：逗号分隔的多个 CTE 可并行定义，后续 CTE 可引用先前定义的 CTE。二、语法逐层拆解1. CTE 名称 (cte_name)作用：为临时结果集命名。规则：遵循 SQL 标识符命名规范（字母开头，可含数字、下划线）。作用域：仅在 WITH 子句后的 单条主查询 中有效。2. 列别名列表 (col1, col2, ...)作用：重命名 CTE 输出列，可用于：为无名称表达式（如 COUNT(*)）赋予名称统一不同 CTE 间的列名注意：别名数量需与 SELECT 子句的列数一致。不指定时使用原始列名。3. CTE 查询语句 (SELECT ...)作用：定义 CTE 的数据来源和计算逻辑。能力：支持所有标准 SELECT 功能（JOIN, WHERE, GROUP BY 等）。可引用其他已定义的 CTE（在同一 WITH 块中）。限制：不可包含 ORDER BY（除非使用 TOP/LIMIT）。递归 CTE 有额外要求（见下文递归部分）。4. 主查询作用：作为 SQL 语句的入口，引用 CTE 进行处理。支持语句类型：SELECTINSERT / UPDATE / DELETE（如 WITH ... DELETE FROM ...）CREATE VIEW5. 递归 CTE 扩展语法WITH RECURSIVE cte_name (col1, ...) AS ( -- 锚点成员 (Anchor Member) SELECT ... -- 初始化结果集 UNION ALL -- 或 UNION（去重） -- 递归成员 (Recursive Member) SELECT ... FROM cte_name WHERE ... -- 引用自身)SELECT ... FROM cte_name;RECURSIVE 关键字（部分数据库如 PostgreSQL 要求显式声明）。锚点成员：初始化递归的基础数据（不引用自身）。递归成员：引用自身 cte_name 实现迭代。必须通过 UNION [ALL] 连接锚点成员。终止条件：通过 WHERE 子句限制递归深度（如 WHERE level < 10）。三、应用场景分析

场景类型

描述

示例用途

1. 代码模块化

分解复杂查询为多步骤逻辑

多级聚合、分段计算

2. 递归查询

处理树形/图状数据

组织架构层级遍历、BOM 展开

3. 重复引用优化

避免相同子查询被多次执行

主查询中多处引用相同中间结果

4. 简化窗口函数

预计算窗口函数结果供后续过滤/聚合

按分区排名后筛选 Top N

5. 替代视图

临时定义无需存储的视图结构

一次性复杂查询

四、创建模拟数据1. 模拟数据库表结构CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), manager_id INT REFERENCES employees(id), -- 自引用，构建树形结构 department_id INT);CREATE TABLE departments ( id INT PRIMARY KEY, name VARCHAR(50), budget DECIMAL(10, 2));CREATE TABLE sales ( id INT PRIMARY KEY, employee_id INT REFERENCES employees(id), amount DECIMAL(10, 2), sale_date DATE);2. 插入模拟数据-- 部门数据INSERT INTO departments (id, name, budget) VALUES(1, 'Engineering', 100000),(2, 'Sales', 80000);-- 员工数据（树形结构）INSERT INTO employees (id, name, manager_id, department_id) VALUES(1, 'Alice', NULL, 1), -- 顶层(2, 'Bob', 1, 1), -- Alice的下属(3, 'Charlie', 1, 1), -- Alice的下属(4, 'David', 2, 1), -- Bob的下属(5, 'Eve', 3, 1), -- Charlie的下属(6, 'Frank', NULL, 2); -- Sales部门独立-- 销售记录INSERT INTO sales (id, employee_id, amount, sale_date) VALUES(1, 2, 5000, '2023-01-15'),(2, 3, 3000, '2023-02-20'),(3, 4, 1500, '2023-03-10'),(4, 5, 2500, '2023-04-05'),(5, 6, 7000, '2023-01-22');五、WITH子句(公用表表达式CTE)应用示例1、基本CTE（模块化复杂聚合）WITH DeptSales AS ( SELECT e.department_id, d.name AS dept_name, SUM(s.amount) AS total_sales, AVG(s.amount) AS avg_sale FROM employees e JOIN sales s ON e.id = s.employee_id JOIN departments d ON e.department_id = d.id GROUP BY e.department_id, d.name)SELECT dept_name, total_sales, avg_sale, budget, total_sales / budget AS efficiency_ratioFROM DeptSalesJOIN departments d ON DeptSales.department_id = d.id;

输出结果：

dept_name

total_sales

avg_sale

budget

efficiency_ratio

Engineering

12000.00

3000.00

100000

0.12

Sales

7000.00

7000.00

80000

0.0875

2、多CTE链式调用WITH EmployeeSales AS ( SELECT e.id, e.name, SUM(s.amount) AS total_sales FROM employees e LEFT JOIN sales s ON e.id = s.employee_id GROUP BY e.id, e.name),RankedSales AS ( SELECT *, RANK() OVER (ORDER BY total_sales DESC) AS sales_rank FROM EmployeeSales)SELECT name, total_sales, sales_rankFROM RankedSalesWHERE sales_rank <= 3;

输出结果：

name

total_sales

sales_rank

Frank

7000.00

1

Bob

5000.00

2

Charlie

3000.00

3

3、递归CTE（遍历组织架构）WITH RECURSIVE OrgChart AS ( -- 锚点成员：顶级管理者 (manager_id IS NULL) SELECT id, name, manager_id, 1 AS level, name::TEXT AS path -- 显式转换为TEXT类型 FROM employees WHERE manager_id IS NULL UNION ALL -- 递归成员：逐级向下查找 SELECT e.id, e.name, e.manager_id, oc.level + 1, oc.path || ' -> ' || e.name -- 路径拼接 FROM employees e INNER JOIN OrgChart oc ON e.manager_id = oc.id)SELECT * FROM OrgChart ORDER BY path;

输出结果：

id

name

manager_id

level

path

1

Alice

NULL

1

Alice

2

Bob

1

2

Alice -> Bob

4

David

2

3

Alice -> Bob -> David

3

Charlie

1

2

Alice -> Charlie

5

Eve

3

3

Alice -> Charlie -> Eve

6

Frank

NULL

1

Frank

说明：

level 列表示在树形结构中的深度（从1开始）。

path 列动态拼接了从根节点到当前节点的完整路径。

结果自然按层级排序（因递归顺序生成）。

六、注意事项数据库兼容性：递归 CTE 支持：MySQL (8.0+), PostgreSQL, SQL Server, Oracle, SQLite。RECURSIVE 关键字：PostgreSQL 要求显式声明，其他通常可省略。性能优化：递归深度：通过 WHERE 子句限制避免无限循环（如 WHERE level < 100）。索引：确保递归连接字段（如 manager_id）有索引。与临时表对比：特性CTE临时表生命周期单次查询内有效会话/事务级有效数据持久化否是索引支持部分数据库支持（如 SQL Server）是递归查询支持原生支持需手动循环填充实践指南：对复杂查询进行分步骤命名（如 EmployeeSales、DeptSummary）。递归 CTE 中确保终止条件明确。替代深度嵌套子查询，提升可读性和维护性。结论

WITH子句(公用表表达式CTE)的价值在于通过 命名化、模块化 的临时结果集，增强复杂 SQL 的可读性与结构清晰度。特别是在递归查询场景中，它提供了传统 SQL 无法比拟的 层级遍历能力（如组织架构、BOM 展开），在工程中替代低效的嵌套查询或存储过程循环。