SQL Server CTE 語法與遞歸查詢完整教學

通用資料表運算式 (CTE) 是定義於單一陳述式執行範圍內的暫時結果集，不儲存為物件，僅於查詢期間有效。支援自我參考與多次引用，適合簡化複雜查詢與實作遞歸邏輯。

基本 CTE 結構

WITH CTE名稱 [(欄位清單)]
AS
(
    查詢定義
)
後續查詢引用 CTE名稱;

範例：簡單 CTE

WITH TEST_CTE AS (SELECT * FROM test4)
SELECT * FROM TEST_CTE;

指定欄位別名

WITH TEST_CTE(id) AS (SELECT id FROM test4)
SELECT * FROM TEST_CTE;

遞歸 CTE 查詢階層資料

建立父子關係測試表：

CREATE TABLE test5 (id INT, name VARCHAR(50), parentid INT);
INSERT INTO test5 VALUES
(1,'父類1',0),(2,'父類2',0),(3,'父類3',0),
(11,'子類11',1),(12,'子類12',1),
(111,'子子類111',11),
(22,'子類22',2),(222,'子子類222',22);

查詢「父類1」及其所有後代：

WITH Test_Recursion(id, name, parentid, [level])
AS
(
    -- 錨點成員：起始節點
    SELECT id, name, parentid, 0 FROM test5 WHERE id = 1
    UNION ALL
    -- 遞歸成員：自我聯結
    SELECT a.id, a.name, a.parentid, b.[level] + 1
    FROM test5 a
    JOIN Test_Recursion b ON a.parentid = b.id
)
SELECT * FROM Test_Recursion;

結果：

關鍵規則

• 錨點成員（不參考 CTE 本身）必須置於第一個 UNION ALL 之前。
• 遞歸成員與錨點成員間必須用 UNION ALL 連接。
• 預設最大遞歸層數 100，可用 OPTION (MAXRECURSION n) 調整。