结构化查询语言(Structured Query Language)

WITH 子句简介

WITH 子句 是 SQL 的一个扩展语法，用于为 SELECT、UPDATE、INSERT 或 DELETE 语句定义一个或多个“临时视图”（inline views）。这些临时视图的作用范围仅限于当前语句，因此它们具有临时性。其主要目的是简化复杂查询的表达，使语句意图更加清晰。此外，当复杂查询中多次出现相同的子查询时，通过使用单一的 WITH 子句，DBMS 可以更高效地优化执行计划。

换句话说，WITH 子句本身并没有新增功能（递归查询是个例外，它将在下一章说明）。它只是提供了一种更加清晰表达复杂查询的语法形式。

提示：SQL 标准将该语法称为 WITH 子句。然而，在实际应用中，这一结构通常被称为 公共表表达式（Common Table Expression，CTE），有时也被称为内联视图（Inline View）。Oracle 将其称为 子查询分解子句（Subquery Factoring Clause）。尽管不是官方术语，但由于 CTE 简洁且易于理解，本页面使用该术语。

语法

WITH cte_name [(temp_column_name [,...])] AS 
  (SELECT ...)
SELECT ... FROM cte_name;

1. WITH： 引入一个 CTE 定义。
2. CTE 名称： CTE 可以有任意名称（如 cte_name）。
3. 子查询： 使用 SELECT 定义 CTE 的数据来源。CTE 是一种逻辑视图，不会实际存储数据。
4. 主查询： 在主查询中可以直接使用 CTE 的名称，如同使用表或视图。

示例

示例 1：输出 CTE 内容

定义一个名为 ambiguous_date 的 CTE，查询在某些日期出生的人员数量，并筛选出同一天出生人数超过 1 的日期：

-- 定义 CTE
WITH ambiguous_date AS
(SELECT count(*) AS cnt_per_date, date_of_birth
  FROM person
  GROUP BY date_of_birth
  HAVING count(*) > 1
)
-- 使用 CTE
SELECT *
  FROM ambiguous_date;

结果： CTE 和视图类似，但范围仅限于当前语句，执行完成后 CTE 自动失效。

示例 2：CTE 用于子查询

将上例的 ambiguous_date CTE 用于主查询的子查询，同时对 CTE 数据添加 BETWEEN 筛选条件：

WITH ambiguous_date AS
(SELECT count(*) AS cnt_per_date, date_of_birth
  FROM person
  GROUP BY date_of_birth
  HAVING count(*) > 1
)
SELECT *
  FROM person p
  WHERE p.date_of_birth IN
    (SELECT date_of_birth
      FROM ambiguous_date
      WHERE cnt_per_date BETWEEN 3 AND 10
    );

示例 3：CTE 在语句中的多次使用

在主查询中不同位置多次使用同一个 CTE：

WITH ambiguous_date AS
(SELECT count(*) AS cnt_per_date, date_of_birth
  FROM person
  GROUP BY date_of_birth
  HAVING count(*) > 1
)
SELECT p.*, 
       (SELECT count(*) 
          FROM ambiguous_date) AS number_of_ambiguous_dates
  FROM person p
  WHERE p.date_of_birth IN 
       (SELECT date_of_birth
          FROM ambiguous_date);

解释：

• 查询返回所有出生日期与 CTE 中标记的“模糊日期”一致的人员。
• 同时，附加显示有多少个“模糊日期”。

扩展

WITH 子句是 递归查询 的基础。这使得 SQL 能够实现更复杂的递归操作，如树形结构处理、分层查询等，相关内容将在下一章节说明。

总结：

• WITH 子句 是一种逻辑工具，用于简化复杂查询。
• 临时性：CTE 的作用范围仅限于当前 SQL 语句。
• 优化执行：通过减少重复子查询，提高了查询的可读性和执行效率。