結構化查詢語言/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 子句

遞迴