SQL Server 2005 CTE (Common Table Expressions) 進階應用指南

CTE 簡介

通用資料表運算式，SQL 2005 新功能。類似別名但功能更強：臨時結果集，僅存在於該陳述式中。可用於 SELECT/INSERT/DELETE/UPDATE/CREATE VIEW。優於衍生表：可自我參考、可多次引用、可對衍生欄位分組、支援遞迴查詢。

建立語法

WITH CTE_name [ (column_name [,...n]) ]
AS ( CTE_query_specification )

欄位名唯一時可省略欄位清單，亦可重新命名。

實戰範例：AdventureWorks 員工與主管查詢

員工資料分散於 HumanResources.Employee 與 Person.Contact，員工與主管同表。

步驟 1：建立員工 CTE

WITH cte_Employee AS (
  SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName,
         e.Title AS JobTitle, c.Phone, e.ManagerID
  FROM HumanResources.Employee e
  INNER JOIN Person.Contact c ON c.ContactID = e.ContactID
)

步驟 2：自我聯結取得主管姓名

WITH cte_Employee AS ( ... )
SELECT E.FirstName + ' ' + E.LastName AS Employee,
       M.FirstName + ' ' + M.LastName AS Manager
FROM cte_Employee E
LEFT OUTER JOIN cte_Employee M ON E.ManagerID = M.EmployeeID

限制與總結

• 單一陳述式中不可建立兩個 CTE
• CTE 提升可讀性、可維護性、降低複雜度，支援多次引用與遞迴