SQL Server WITH AS (CTE) 實戰：簡化巢狀查詢、提升可讀性、避免暫存表效能損耗

什麼是 CTE (Common Table Expression)？

WITH ... AS (...) 定義一個僅在緊隨其後的單一陳述式可見的暫時結果集。不同於暫存表/表變數，CTE：

• 不佔用 tempdb 物理儲存 (除非查詢最佳化工具決定物質化)。
• 支援遞歸。
• 可在同一 WITH 定義多個 CTE，互相參考。
• 語法清晰，將複雜邏輯模組化。

基本語法

WITH cte_name (col1, col2) AS (
    SELECT col1, col2 FROM SomeTable WHERE ...
)
SELECT * FROM cte_name;

案例一：替換巢狀子查詢

-- 原始巢狀
SELECT * FROM Person.StateProvince
WHERE CountryRegionCode IN (
    SELECT CountryRegionCode FROM Person.CountryRegion WHERE Name LIKE 'C%'
);

-- CTE 版 (可讀性大幅提升)
WITH CCountries AS (
    SELECT CountryRegionCode FROM Person.CountryRegion WHERE Name LIKE 'C%'
)
SELECT * FROM Person.StateProvince
WHERE CountryRegionCode IN (SELECT CountryRegionCode FROM CCountries);

案例二：替代表變數避免 I/O 開銷

-- 表變數版 (寫入 tempdb、統計資訊差)
DECLARE @t TABLE (CountryRegionCode NVARCHAR(3));
INSERT INTO @t SELECT CountryRegionCode FROM Person.CountryRegion WHERE Name LIKE 'C%';
SELECT * FROM Person.StateProvince WHERE CountryRegionCode IN (SELECT * FROM @t);

-- CTE 版 (最佳化器可直接展開、無額外 I/O)
WITH CCountries AS (
    SELECT CountryRegionCode FROM Person.CountryRegion WHERE Name LIKE 'C%'
)
SELECT * FROM Person.StateProvince WHERE CountryRegionCode IN (SELECT CountryRegionCode FROM CCountries);

進階：多 CTE 串聯與遞歸

多 CTE (單一 WITH)

WITH
C1 AS (SELECT * FROM Table1 WHERE Type = 'A'),
C2 AS (SELECT * FROM Table2 WHERE Status = 1),
C3 AS (SELECT * FROM Table3 WHERE Date > '2024-01-01')
SELECT a.*, b.*, c.*
FROM C1 a JOIN C2 b ON a.ID = b.ID JOIN C3 c ON a.ID = c.ID;

遞歸 CTE：組織架構樹

WITH OrgTree AS (
    -- Anchor member
    SELECT EmpID, MgrID, EmpName, 0 AS Level
    FROM Employee WHERE MgrID IS NULL
    UNION ALL
    -- Recursive member
    SELECT e.EmpID, e.MgrID, e.EmpName, ot.Level + 1
    FROM Employee e
    INNER JOIN OrgTree ot ON e.MgrID = ot.EmpID
)
SELECT * FROM OrgTree OPTION (MAXRECURSION 100);

MAXRECURSION 0 移除 100 層限制 (謹慎使用，防無限遞歸)。

關鍵限制與注意事項

1. CTE 定義後必須緊接單一 SELECT/INSERT/UPDATE/DELETE/MERGE，中間不可有其他陳述式。
2. CTE 定義內不可使用：COMPUTE、ORDER BY (除非搭配 TOP)、INTO、OPTION 查詢提示、FOR XML/BROWSE。
3. 若前一個批次陳述式未以分號結尾，緊接的 WITH 必須前置分號：;WITH CTE AS ...。
4. 同名 CTE 與資料表並存時，緊接查詢優先使用 CTE；後續查詢則解析為資料表。
5. CTE 可自我參考 (遞歸)，但不可前向參考 (在定義前使用)。

效能迷思釐清

• CTE 不保證 比子查詢快；它本質上是語法糖，最佳化器通常會展開為等價子查詢計畫。
• 若 CTE 被多次參考，最佳化器可能選擇物質化 (寫入 tempdb worktable)；此時效能類似暫存表。
• 真正提升效能的關鍵在於：正確索引、統計資訊更新、避免 RBAR、集合式思維，而非單純改寫語法。

最佳實務建議

1. 用 CTE 提升可讀性、模組化複雜邏輯，而非盲目追求效能。
2. 遞歸查詢首選 CTE，語法最清晰。
3. 大量資料多次重用的中間結果，考慮暫存表 + 索引而非 CTE。
4. 習慣在 WITH 前加分號 ;WITH，防止批次合併執行時語法錯誤。