结构化查询语言/with 子句

WITH 子句通过定义一个或多个“内联视图”来扩展 SELECT、UPDATE、INSERT 或 DELETE 语句。这种“内联视图”的作用域仅限于实际语句。因此它们具有临时性质。它们的目的是重新排列复杂的语句，以便更清楚地表示完整语句的意图。主要而言，它是一个语法元素，用于支持数据库应用程序的维护。其次，如果复杂的语句在不同的位置包含相同的短语，并且它们通过单个WITH 子句重新排列，则 DBMS 更可能找到最佳的执行策略。

换句话说：WITH 子句除了在下一章将要解释的递归查询之外，没有提供任何新功能。它只提供了一个语法元素来以清晰的方式表达复杂的查询。

提示：WITH 子句是 SQL 标准的术语。在日常用语中，这种语言构造通常被称为公用表表达式 (CTE)，有时也称为内联视图。Oracle 将其称为子查询分解子句。虽然这不是官方术语，但在维基教科书的此页面上，我们更喜欢术语CTE，因为它是一个有表达力的缩写。

语法

WITH cte_1 [(temp_column_name [,...])] AS 
  (SELECT ...)
SELECT ... FROM cte_1
; -- consider the semicolon: the SELECT command (containing a WITH clause) runs from the term 'WITH' up to here.

关键字 WITH 引入 CTE 的定义。首先，CTE 获得一个任意的名称 - 在我们的例子中是“cte_1”。其后是一个 SELECT 语句，它定义了如何从一个或多个表中检索数据到 CTE（请注意：这只是一个定义 - 不必实际将数据物化到 CTE 中）。之后，是一个正常的 SELECT、UPDATE、INSERT 或 DELETE 语句，其中之前定义的 CTE 名称直接在 FROM 中或任何其他允许使用视图或表名称的地方使用。

例子

第一个例子仅限于报告CTE的内容。在这种情况下，它包含有关多人出生的日期的信息，并包含带有日期和计数器的行。

-- define the CTE 'ambiguous_date'
WITH ambiguous_date AS  -- this is similar to: CREATE VIEW ambiguous_date AS ...
(SELECT count(*) AS cnt_per_date, date_of_birth
  FROM person
  GROUP BY date_of_birth
  HAVING count(*) > 1
)
-- use the CTE 'ambiguous_date'
SELECT *
  FROM ambiguous_date
;
-- After the semicolon, the CTE is no longer known by the DBMS. It is gone out of scope.

CTE和视图之间的相似性是显而易见的。主要区别在于视图定义在语句中使用视图名称后保持活动，而CTE的作用域仅限于定义它的语句。这就是有些人将CTE称为内联视图的原因。

第二个例子在主 SELECT 的子 SELECT 中使用CTE。CTE 通过 BETWEEN 条件进一步修改。

-- define the CTE
WITH ambiguous_date AS
(SELECT count(*) AS cnt_per_date, date_of_birth
  FROM person
  GROUP BY date_of_birth
  HAVING count(*) > 1
)
-- the main SELECT
SELECT *
  FROM person p
  WHERE p.date_of_birth IN
    -- use the CTE
    (SELECT date_of_birth
      FROM ambiguous_date
      --  one can manipulate the CTE in the same manner as every other view or table
      WHERE cnt_per_date BETWEEN 3 AND 10
    )
;

第三个例子在语句中的不同位置使用CTE。

-- define the CTE
WITH ambiguous_date AS
(SELECT count(*) AS cnt_per_date, date_of_birth
  FROM person
  GROUP BY date_of_birth
  HAVING count(*) > 1
)
-- use of the CTE at different places and for different purposes
SELECT p.*, (SELECT count(*) AS sum_of_ambiguity_dates
               FROM ambiguous_date
            ) AS number_of_ambiguous_dates
  FROM person p
  WHERE p.date_of_birth IN 
       (SELECT date_of_birth
          FROM ambiguous_date
       )
;

主 SELECT 检索所有具有相同生日的人（超过两行），这些生日由CTE识别（两行）。它还用模糊生日的次数（即“2”）来修饰每个人。

扩展

WITH 子句是递归查询的基础，这将在下一章中解释。

窗口函数

结构化查询语言
with 子句

递归