SQL 使用 CTE (Common Table Expression)

March 13, 2023

SQL Server

CTE (Common Table Expression) 中文是一般資料表運算式，可以用來查詢資料，做暫時性的使用。

使用方式

以下為一個 CTE 的範例，WITH 後方接表達式名稱，括號內填入需要的欄位，AS 內接入 SELECT 語法。後方接一般的 SQL 語法。

WITH Expression (Column1, Column2, ...)
AS
(
    SELECT ...
)
SQL Statement;

以之前的範例 (SQL 計算與上一筆的差值) 為例：

WITH MonthSavingOrder (YearMonth, Saving, ID)
AS
(
    SELECT YearMonth, Saving
        , ROW_NUMBER() OVER (ORDER BY YearMonth ASC) AS ID 
    FROM MonthSaving M
)
SELECT MS.YearMonth, MS.Saving, MSO.Saving - MSO2.Saving AS Income
FROM MonthSaving MS
INNER JOIN MonthSavingOrder MSO ON MS.YearMonth = MSO.YearMonth
LEFT JOIN MonthSavingOrder MSO2 ON MSO.ID = (MSO2.ID + 1)

特性

  • 適合用於增加 SQL 程式可讀性，用在一次性的暫時性資料。
  • 如果 CTE 前面還有其它 SQL 語法，則 CTE 前方需要加一個 ; 號。
  • CTE 只能用在其後的第一組 SQL 語法，但第一組語法可以多次取用 CTE (可做為遞迴使用)。
  • 在 Store Procedure 內，CTE 仍然會每次執行都重新編譯，因此可能會有效能的問題。
  • CTE 不會儲存成資料庫物件，生命週期僅限於查詢期間。

參考資料