`WITH` 查询（公共表表达式）

WITH 用于在一条 SQL 语句中给中间结果命名。这个命名结果称为公共表表达式（Common Table Expression，CTE）：

WITH customer_totals AS (
  SELECT customer_id, SUM(total_amount) AS paid_amount
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id
)
SELECT c.customer_id, c.name, t.paid_amount
FROM customers AS c
JOIN customer_totals AS t ON t.customer_id = c.customer_id
WHERE t.paid_amount >= 1000;

可以把 customer_totals 理解为只在当前语句中可见的临时查询名称。CTE 主要用于组织逻辑、复用中间结果和表达递归；它不会自动创建永久表。

基本语法

普通 CTE 的结构是：

WITH cte_name [(column_name, ...)] AS (
  query
)
main_query;

CTE 定义在主查询之前，主查询可以像读取表或视图一样读取它：

WITH paid_orders AS (
  SELECT order_id, customer_id, total_amount
  FROM orders
  WHERE status = 'paid'
)
SELECT order_id, total_amount
FROM paid_orders
WHERE total_amount >= 100;

CTE 的作用域通常只覆盖紧随其后的一条语句。下一条独立 SQL 不能继续使用 paid_orders。

为 CTE 列命名

默认情况下，CTE 的列名来自内部查询的输出列：

WITH totals AS (
  SELECT customer_id, SUM(total_amount) AS paid_amount
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, paid_amount
FROM totals;

也可以在 CTE 名称后统一声明：

WITH totals(customer_id, paid_amount) AS (
  SELECT customer_id, SUM(total_amount)
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, paid_amount
FROM totals;

列名数量必须与查询输出列数一致。递归 CTE 或 VALUES CTE 通常更适合显式列名，因为所有分支需要共享同一输出结构。

在一个 `WITH` 中定义多个 CTE

多个 CTE 使用逗号分隔：

WITH
paid_orders AS (
  SELECT order_id, customer_id, total_amount
  FROM orders
  WHERE status = 'paid'
),
customer_totals AS (
  SELECT customer_id, SUM(total_amount) AS paid_amount
  FROM paid_orders
  GROUP BY customer_id
),
vip_customers AS (
  SELECT customer_id, paid_amount
  FROM customer_totals
  WHERE paid_amount >= 1000
)
SELECT c.customer_id, c.name, v.paid_amount
FROM customers AS c
JOIN vip_customers AS v ON v.customer_id = c.customer_id;

后面的 CTE 通常可以引用前面已经定义的 CTE。是否允许引用后面才定义的 CTE、相互递归以及如何声明，存在数据库差异，不应依赖未经确认的前向引用。

命名应表达这一步“产生了什么”，例如 paid_orders、customer_totals，而不是 temp1、step2。每个 CTE 最好只承担一个容易验证的转换。

CTE 与派生表、视图、临时表

这些工具都能组织查询，但生命周期和能力不同：

工具	典型作用域	是否保存定义或数据	适合场景
派生表	当前查询的局部层	否	一次使用的简单中间结果
CTE	当前一条语句	否	多步骤命名、复用、递归
视图	多条语句长期使用	保存查询定义	共享查询接口和权限封装
临时表	会话或事务	保存数据	多语句复用、建立索引、分阶段处理

CTE 并不等于临时表。数据库可能把它展开到主查询，也可能先计算并保存中间结果；是否写入临时存储由产品、版本、查询形状和优化器决定。

CTE 不保证执行顺序

CTE 的书写顺序表达依赖关系和阅读顺序，但普通只读 CTE 不是过程式步骤：

WITH a AS (...), b AS (...)
SELECT ...;

不能据此假设数据库一定先完整执行 a、再完整执行 b、最后执行主查询。优化器可能内联、合并、重排或删除无用计算。不要依赖函数副作用、行的自然顺序或某个 CTE 的实际执行次数。

最终结果需要顺序时，仍应在最外层使用 ORDER BY。

物化与优化

CTE 是否物化是性能分析中的关键问题：

内联或展开：CTE 像普通子查询一样并入主查询，外层过滤条件有机会下推；
物化：先计算 CTE 并暂存结果，后续再读取，可能避免重复计算，也可能阻止优化。

不同数据库和版本采用不同规则。即使同一个产品，也可能根据 CTE 被引用次数、递归、函数特性或成本估算选择不同策略。

部分 PostgreSQL 版本提供 MATERIALIZED 和 NOT MATERIALIZED 控制提示：

WITH recent_orders AS MATERIALIZED (
  SELECT order_id, customer_id, created_at
  FROM orders
  WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT *
FROM recent_orders;

这些不是通用 SQL，也不应在未查看执行计划前机械添加。判断 CTE 性能时，应使用目标数据库的 EXPLAIN，观察过滤是否下推、是否重复扫描、估算行数和临时存储。

什么时候普通 CTE 更清楚

CTE 适合把复杂查询拆成可命名阶段：

WITH
item_amounts AS (
  SELECT
    order_id,
    unit_price * quantity AS line_amount
  FROM order_items
),
order_totals AS (
  SELECT order_id, SUM(line_amount) AS order_amount
  FROM item_amounts
  GROUP BY order_id
)
SELECT o.order_id, t.order_amount
FROM orders AS o
JOIN order_totals AS t ON t.order_id = o.order_id
WHERE t.order_amount >= 500
ORDER BY t.order_amount DESC, o.order_id;

这里每层都有明确职责：先算行金额，再算订单总额，最后关联订单并筛选。相比深层嵌套括号，CTE 更便于单独运行某一步验证结果。

但不要把每个简单子句都拆成一个 CTE。层次过多会迫使读者在大量名称间跳转，也可能增加优化不确定性。

递归 CTE 的用途

递归 CTE 可以让查询引用自身，常用于：

组织结构、分类树和菜单树；
图的可达路径；
物料清单；
连续日期或数字序列；
沿父子关系向上或向下查找。

基本结构由两个部分组成：

WITH RECURSIVE cte_name AS (
  non_recursive_term

  UNION ALL

  recursive_term
)
SELECT *
FROM cte_name;

非递归部分也称锚点，提供起始行；递归部分读取上一轮产生的行，并生成下一轮行。

递归示例：员工组织树

假设员工表包含直属主管编号：

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  manager_id INTEGER,
  name VARCHAR(100) NOT NULL
);

从编号为 1 的负责人开始向下查询所有下属：

WITH RECURSIVE organization AS (
  -- 锚点：找到起始员工
  SELECT
    employee_id,
    manager_id,
    name,
    0 AS depth
  FROM employees
  WHERE employee_id = 1

  UNION ALL

  -- 递归项：找到上一层员工的直接下属
  SELECT
    e.employee_id,
    e.manager_id,
    e.name,
    o.depth + 1
  FROM employees AS e
  JOIN organization AS o ON e.manager_id = o.employee_id
)
SELECT employee_id, manager_id, name, depth
FROM organization
ORDER BY depth, employee_id;

递归过程可以概念化为：

执行锚点，得到负责人；
把当前轮结果提供给递归项，找到直属下属；
用新产生的行继续下一轮；
某一轮不再产生新行时停止；
合并所有轮次的结果。

这是一种理解模型，不代表数据库必须按某种外部可观察顺序输出行。深度优先或广度优先的最终展示仍需显式排序。

为什么递归通常使用 `UNION ALL`

UNION ALL 保留每轮产生的所有行，通常更快，也能表达不同路径到达同一节点的情况。普通 UNION 会对累计结果去重，有时能阻止某些重复状态继续递归，但会增加去重成本，而且不能替代完整的环检测。

选择哪一个取决于业务语义：

树结构中每个节点只有一个父节点，通常使用 UNION ALL；
图结构可能由多条路径到达同一节点，需要决定保留路径还是只保留节点；
行中包含深度或路径时，即使节点相同，整行也不同，UNION 未必能终止循环。

递归必须保证终止

如果数据中存在环，或者递归条件不断产生新状态，查询可能运行很久，直到达到数据库递归上限、耗尽资源或被取消。

生成有限数字序列时，要写出清楚的边界：

WITH RECURSIVE numbers(n) AS (
  VALUES (1)

  UNION ALL

  SELECT n + 1
  FROM numbers
  WHERE n < 10
)
SELECT n
FROM numbers
ORDER BY n;

这里 WHERE n < 10 确保递归停止。处理未知层级的图时，应记录访问路径，并拒绝再次访问路径中的节点。数组、字符串路径、JSON 或专用 CYCLE 语法的实现因数据库而异。

SQL 标准定义了 SEARCH 和 CYCLE 子句来辅助搜索顺序与环检测，但产品支持程度和语法细节不同。生产递归查询应同时依靠数据约束、环检测、最大深度和执行超时形成保护。

深度、路径和展示顺序

递归结果常需要附带深度或路径：

WITH RECURSIVE category_tree AS (
  SELECT
    category_id,
    parent_id,
    name,
    0 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT
    c.category_id,
    c.parent_id,
    c.name,
    t.depth + 1
  FROM categories AS c
  JOIN category_tree AS t ON c.parent_id = t.category_id
)
SELECT category_id, parent_id, name, depth
FROM category_tree
ORDER BY depth, category_id;

ORDER BY depth, category_id 提供近似广度优先的展示，但不代表递归本身按此顺序执行。精确的深度优先顺序通常需要构造可排序路径；路径类型与拼接方式取决于数据库。

递归 CTE 的方言差异

不同数据库的主要差异包括：

PostgreSQL、MySQL 和 SQLite 等通常写 WITH RECURSIVE；
SQL Server 的递归 CTE 通常只写 WITH，不使用 RECURSIVE 关键字；
默认最大递归层数及调整方式不同；
递归项是否允许聚合、窗口函数、外连接、多个自引用等限制不同；
数组路径、SEARCH、CYCLE 和生成序列函数并不通用。

递归查询迁移前必须查看目标数据库版本文档，并用包含空树、深树、重复边和环的数据测试。

CTE 与数据修改语句

很多数据库允许 CTE 为 INSERT、UPDATE、DELETE 或 MERGE 提供输入：

WITH expired_orders AS (
  SELECT order_id
  FROM orders
  WHERE status = 'pending'
    AND expires_at < CURRENT_TIMESTAMP
)
UPDATE orders
SET status = 'expired'
WHERE order_id IN (
  SELECT order_id
  FROM expired_orders
);

这是“CTE 内查询，主语句修改”的常见形式。

部分数据库还允许 CTE 本身执行数据修改并通过 RETURNING 或类似机制把结果传给主语句，例如 PostgreSQL 支持某些数据修改型 CTE。此能力不是通用 SQL，不同 CTE 之间的执行关系、可见性和并发语义也更复杂：

-- PostgreSQL 风格示例，不能直接假定可跨数据库运行
WITH moved_rows AS (
  DELETE FROM orders
  WHERE created_at < DATE '2020-01-01'
  RETURNING order_id, customer_id, total_amount, created_at
)
INSERT INTO archived_orders
SELECT order_id, customer_id, total_amount, created_at
FROM moved_rows;

涉及数据移动时，还要考虑目标约束、触发器、事务原子性、失败重试和并发写入。不要仅因为语句更短就替代经过验证的事务流程。

CTE 与权限、快照和副作用

CTE 不会绕过底层对象的权限。查询者仍需具备数据库要求的表、列或函数权限。

同一语句中的数据读取通常遵循该数据库对语句快照和隔离级别的规则，但数据修改型 CTE 的可见性可能具有产品特定语义。普通 CTE 中调用易变函数时，也不能想当然地认为函数只执行一次；内联、物化和引用次数都可能影响行为。

依赖当前时间、随机值、序列或有副作用函数时，应查阅目标数据库对函数易变性和 CTE 执行的保证。

常见错误

把 CTE 当作自动性能优化

CTE 首先是查询结构工具。它可能改善性能，也可能因物化、重复计算或阻止条件下推而变慢。使用执行计划验证，不要仅凭语法判断。

拆分层次过多

每个 CTE 都应有清楚职责。只把简单列重命名也拆成多层，会增加阅读成本。

假设 CTE 保留内部排序

CTE 内部的 ORDER BY 不会自动成为最终结果顺序。最外层需要再次排序。

递归缺少终止条件

处理层级和图时，要考虑脏数据中的自环、双向环和超深链路，并配置深度或时间保护。

递归锚点与递归项类型不一致

UNION 两侧的列数、顺序和类型必须兼容。路径字符串或数值深度不断增长时，还要确保类型容量足够。

混淆 CTE 与临时表

CTE 只在一条语句中可见，通常不能单独建立索引，也不保证物化。跨多条语句复用或需要统计信息时，应评估临时表。

检查清单

普通 CTE

每个名称是否准确描述中间结果；
层次是否让查询更容易读和验证；
多次引用时是否了解目标数据库的物化策略；
最终排序是否放在最外层；
性能是否通过实际执行计划验证。

递归 CTE

锚点是否只产生正确的起始行；
递归连接是否从当前层推进到下一层；
两侧列数和类型是否一致；
是否有明确终止条件、环检测和深度保护；
应使用 UNION ALL 还是需要集合去重；
路径、深度和最终展示顺序是否满足业务需求；
是否测试了空结果、叶子节点、深链、重复路径和环。

CTE 的价值在于为复杂关系查询建立清楚的命名层次，而递归 CTE 则把“从当前结果继续查找下一层”表达在一条语句中。使用它们时，应把可读性、终止条件和实际执行计划放在同等重要的位置。

基本语法​

为 CTE 列命名​

在一个 WITH 中定义多个 CTE​

CTE 与派生表、视图、临时表​

CTE 不保证执行顺序​

物化与优化​

什么时候普通 CTE 更清楚​

递归 CTE 的用途​

递归示例：员工组织树​

为什么递归通常使用 UNION ALL​

递归必须保证终止​

深度、路径和展示顺序​

递归 CTE 的方言差异​

CTE 与数据修改语句​

CTE 与权限、快照和副作用​

常见错误​

把 CTE 当作自动性能优化​

拆分层次过多​

假设 CTE 保留内部排序​

递归缺少终止条件​

递归锚点与递归项类型不一致​

混淆 CTE 与临时表​

检查清单​

普通 CTE​

递归 CTE​