大家好,前幾天的範例有使用到 CTE (Common Table Expression) ,CTE 可以用來查詢資料,做暫時性的使用。
以下為一個 CTE 的範例,WITH
後方接表達式名稱,括號內填入需要的欄位,AS
內接入 SELECT
語法。後方接一般的 SQL 語法。
WITH Expression (Column1, Column2, ...)
AS
(
SELECT ...
)
SQL Statement;
以之前的範例 (Day 19: 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)
;
號。