SQL Server 2005 實戰技巧：善用 CTE (公用資料表運算式) 簡化複雜巢狀查詢

在撰寫 T-SQL 查詢時，巢狀子查詢層級過深往往會導致程式碼難以閱讀、維護成本高昂。以往開發者常使用暫存表或資料表變數來拆解邏輯，但這樣做會引入額外的 I/O 開銷，影響效能。SQL Server 2005 引入的 公用資料表運算式 完美解決了這個兩難困境：它兼具結構清晰與執行效能優異的雙重優勢。

為什麼需要 CTE？

假設我們要查詢國家代碼以「C」開頭的所有省份資料，傳統寫法如下：

SELECT * 
FROM person.StateProvince 
WHERE CountryRegionCode IN (
    SELECT CountryRegionCode 
    FROM person.CountryRegion 
    WHERE Name LIKE 'C%'
);

若業務邏輯更複雜，巢狀層級疊加會讓 SQL 變得極難維護。早期解法常用資料表變數：

DECLARE @t TABLE (CountryRegionCode NVARCHAR(3));
INSERT INTO @t (CountryRegionCode) 
SELECT CountryRegionCode FROM person.CountryRegion WHERE Name LIKE 'C%';
SELECT * FROM person.StateProvince WHERE CountryRegionCode IN (SELECT * FROM @t);

雖然邏輯分離了，但資料表變數底層使用 TempDB，會產生額外 I/O，不適合大數據量、高頻查詢場景。

CTE 語法與實戰範例

CTE 的基本語法結構：

WITH <common_table_expression> [ ,...n ]
<common_table_expression> ::= 
    expression_name [ ( column_name [ ,...n ] ) ]
    AS ( CTE_query_definition )

同樣的需求改寫為 CTE：

WITH cr AS (
    SELECT CountryRegionCode 
    FROM person.CountryRegion 
    WHERE Name LIKE 'C%'
)
SELECT * 
FROM person.StateProvince 
WHERE CountryRegionCode IN (SELECT * FROM cr);

cr 即為一個公用資料表運算式，使用方式類似資料表變數，但 SQL Server 內部優化機制不同，效能顯著優於資料表變數。

CTE 使用六大關鍵注意事項

1. 緊接執行： CTE 定義後必須立即跟隨使用它的 SELECT、INSERT、UPDATE、DELETE 等陳述式，中間不能插入其他 SQL，否則 CTE 失效。
2. 多重 CTE： 可在單一 WITH 子句中定義多個 CTE，以逗號分隔，彼此可互相引用（僅限向後引用）。
3. 名稱遮蔽： 若 CTE 名稱與實體資料表/檢視表同名，緊隨其後的查詢優先使用 CTE；後續查詢則回歸實體物件。
4. 遞迴與自我參考： CTE 可參考自身或同一 WITH 子句中較早定義的 CTE，支援遞迴查詢（詳細用法將於後續文章介紹）。
5. 定義限制： CTE 查詢定義中不可使用：COMPUTE/COMPUTE BY、ORDER BY（除非搭配 TOP）、INTO、帶查詢提示的 OPTION、FOR XML、FOR BROWSE。
6. 批次處理分號： 若 CTE 屬於批次的一部分，前一條陳述式必須以分號結尾，否則會報錯。

   DECLARE @s NVARCHAR(3); SET @s = 'C%';
   ; -- 必須加分號
   WITH t_tree AS (
       SELECT CountryRegionCode FROM person.CountryRegion WHERE Name LIKE @s
   )
   SELECT * FROM person.StateProvince WHERE CountryRegionCode IN (SELECT * FROM t_tree);