SQL Server 2005 CTE 完全指南：語法、六大實戰模式與遞迴查詢最佳實踐

三、 六大經典實戰模式

1. 結果集別名化（簡化嵌套）

WITH C(OrderYear, CustomerID) AS (
    SELECT YEAR(OrderDate), CustomerID FROM dbo.Orders
)
SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts
FROM C GROUP BY OrderYear;

2. 多重 CTE 串聯（管線化處理）

WITH C1 AS (
    SELECT YEAR(OrderDate) AS OrderYear, CustomerID FROM dbo.Orders
),
C2 AS (
    SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts
    FROM C1 GROUP BY OrderYear
)
SELECT OrderYear, NumCusts FROM C2 WHERE NumCusts > 70;

3. 多重引用（自我 JOIN）

WITH YearlyCount AS (
    SELECT YEAR(OrderDate) AS OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts
    FROM dbo.Orders GROUP BY YEAR(OrderDate)
)
SELECT Cur.OrderYear,
       Cur.NumCusts AS CurNumCusts,
       Prv.NumCusts AS PrvNumCusts,
       Cur.NumCusts - Prv.NumCusts AS Growth
FROM YearlyCount Cur
LEFT JOIN YearlyCount Prv ON Cur.OrderYear = Prv.OrderYear + 1;

同一 CTE 在外層查詢被引用多次，邏輯清晰、避免重複撰寫。

4. 修改資料（INSERT / DELETE）

-- 4.1 交叉產生測試資料並寫入新表
WITH CrossCustomers AS (
    SELECT 1 AS c, C1.* FROM dbo.Customers C1, dbo.Customers C2
)
SELECT ROW_NUMBER() OVER (ORDER BY c) AS KeyCol,
       CustomerID, CompanyName, ContactName, ContactTitle,
       Address, City, Region, PostalCode, Country, Phone, Fax
INTO dbo.CustomersDups FROM CrossCustomers;

-- 4.2 刪除重複，保留每 CustomerID 最大 KeyCol
WITH JustDups AS (
    SELECT * FROM dbo.CustomersDups C1
    WHERE KeyCol < (SELECT MAX(KeyCol) FROM dbo.CustomersDups C2
                    WHERE C2.CustomerID = C1.CustomerID)
)
DELETE FROM JustDups;

5. 物件容器化（封裝與模組化）

CTE 不支援直接巢狀，但可封裝於 View / Inline TVF 中，再由外層 CTE 引用，實現邏輯分層。

CREATE VIEW dbo.VYearCnt AS
WITH YearCnt AS (
    SELECT YEAR(OrderDate) AS OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts
    FROM dbo.Orders GROUP BY YEAR(OrderDate)
)
SELECT * FROM YearCnt;

作者指出：在 View/UDF 中直接使用 CTE 價值有限，因本身已是模組化單元。

6. 遞迴 CTE（階層查詢）

經典案例：給定員工 ID，遞迴查詢其所有下屬。

-- 最佳索引：覆蓋索引避免 Key Lookup
CREATE UNIQUE INDEX idx_mgr_emp ON dbo.Employees(ReportsTo, EmployeeID)
INCLUDE (FirstName, LastName);

-- 遞迴查詢
WITH EmpsCTE AS (
    -- Anchor member：根節點
    SELECT EmployeeID, ReportsTo, FirstName, LastName
    FROM dbo.Employees WHERE EmployeeID = 5
    UNION ALL
    -- Recursive member：下一層
    SELECT E.EmployeeID, E.ReportsTo, E.FirstName, E.LastName
    FROM EmpsCTE MGR
    JOIN dbo.Employees E ON E.ReportsTo = MGR.EmployeeID
)
SELECT * FROM EmpsCTE
OPTION (MAXRECURSION 0); -- 0 = 無限制，預設 100

關鍵點：

• 錨點成員僅執行一次，產生初始集合。
• 遞迴成員重複執行，直到無新資料列產生。
• MAXRECURSION 防止無限迴圈，生產環境建議設定合理上限。
• INCLUDE 欄位建立覆蓋索引，避免書籤查找，大幅提升遞迴效能。

四、 CTE 使用限制與注意事項

• 僅能跟隨單一 SELECT/INSERT/UPDATE/DELETE/ MERGE 陳述式。
• 無法在 CTE 內部宣告另一個 CTE（需改用多重 CTE 串聯）。
• 遞迴 CTE 不支援 DISTINCT、GROUP BY、TOP 等彙總運算子。
• 若前一批次未以分號結尾，必須在 WITH 前補分號。

五、 總結

CTE 是 SQL Server 2005 最具生產力的 T-SQL 增強之一：替代衍生表、簡化遞迴、支援多重引用、提升程式碼模組化。善用顯式欄位清單與遞迴上限控制，能寫出既高效又易維護的查詢。