SQL Server CTE (WITH AS) 完整指南：語法、優化技巧與避坑指南

什麼是 CTE？

Common Table Expression (CTE)，即「公用資料表運算式」，SQL Server 2005 引入。以 WITH 關鍵字開頭，定義一個暫時命名的結果集，僅在緊隨其后的單一陳述式（SELECT/INSERT/UPDATE/DELETE/MERGE）範圍內可見。類似「語句級視圖」，不持久化、不佔用儲存空間。

為什麼用 CTE？

• 可讀性：將複雜子查詢拆解為具名步驟，邏輯清晰
• 可維護性：單一定義、多處引用（同一 WITH 內）
• 效能優於表變數：不強制寫入 tempdb，避免額外 I/O
• 遞迴查詢：原生支援階層/遞迴邏輯（UNION ALL 遞迴成員）

基礎語法

WITH cte_name [(col1, col2, ...)] AS (
    -- 定義查詢
    SELECT ...
)
-- 緊接單一陳述式引用 CTE
SELECT * FROM cte_name;

實戰範例：替換巢狀子查詢

-- 原始：難讀的巢狀子查詢
SELECT * FROM person.StateProvince 
WHERE CountryRegionCode IN (
    SELECT CountryRegionCode FROM person.CountryRegion WHERE Name LIKE 'C%'
);

-- CTE 版：清晰分層
WITH cr AS (
    SELECT CountryRegionCode FROM person.CountryRegion WHERE Name LIKE 'C%'
)
SELECT * FROM person.StateProvince 
WHERE CountryRegionCode IN (SELECT * FROM cr);

多 CTE 串聯

WITH 
cte1 AS (SELECT * FROM table1 WHERE name LIKE 'abc%'),
cte2 AS (SELECT * FROM table2 WHERE id > 20),
cte3 AS (SELECT * FROM table3 WHERE price < 100)
SELECT a.* FROM cte1 a JOIN cte2 b ON a.id = b.id JOIN cte3 c ON a.id = c.id;

⚠️ 必須遵守的 6 大限制

1. 緊接使用：CTE 定義後必須立即跟單一陳述式，中間不能插入其他 SQL
2. 單一 WITH：多個 CTE 用逗號分隔，只能有一個 WITH
3. 名稱遮蔽：同名 CTE 會遮蔽同名資料表/視圖（僅在緊接陳述式生效）
4. 遞迴限制：可自我引用、引用前序 CTE，禁止前向引用
5. 定義查詢禁用子句：COMPUTE、ORDER BY（除非有 TOP）、INTO、OPTION、FOR XML/BROWSE
6. 批次分隔：若 CTE 在批次中非首句，前一句必須以分號結尾

遞迴 CTE 範例（組織架構樹）

WITH OrgTree AS (
    -- 錨點成員：根節點
    SELECT OrganiseUnitID, ParentOrganiseUnitID, OrganiseName, 0 AS Level
    FROM OrganiseUnit WHERE ParentOrganiseUnitID IS NULL
    UNION ALL
    -- 遞迴成員：子節點
    SELECT ou.OrganiseUnitID, ou.ParentOrganiseUnitID, ou.OrganiseName, ot.Level + 1
    FROM OrganiseUnit ou
    INNER JOIN OrgTree ot ON ou.ParentOrganiseUnitID = ot.OrganiseUnitID
)
SELECT * FROM OrgTree ORDER BY Level, OrganiseUnitID;

效能提示

• CTE 非物化（除非被引用 ≥2 次或加 OPTION (HASH JOIN) 等提示），每次引用重新執行
• 大資料量多次引用時，考慮改用暫存表 #temp 或 INSERT INTO #temp SELECT ... FROM CTE 物化
• 遞迴預設最大 100 層，可用 OPTION (MAXRECURSION n) 調整（0 = 無限制）