通用表表達式(CTEs)是SQL Server 2005的一項新功能。它們類似於alias(如在SELECT T1.* FROM MyTable T1中),不過功能更為強大。本質上,CTE是一個臨時結果集,它僅僅存在於它發生的語句中。您可以在SELECT、INSERT、DELETE、UPDATE或CTEATE VIEW語句中建立一個CTE。CTE類似於派生表,但擁有幾項優點。
CTE的優點
與派生表不同,CTE能夠引用自己本身。如果您不必存儲視圖,您可以用一個CTE來代替它。在一個語句中,您還可以多次引用CTE。應用CTE,您可以通過一個派生欄對結果進行分組。
之前,我曾寫過有關原子和分子查詢的文章。原子查詢建立一個表,而分子查詢建立在原子查詢之上,提供清晰與重復利用。應用CTE也可以達到同樣的目的。您可以將查詢區域分割成可讀的“塊”,然後用這些塊建立一個復雜的查詢。執行遞歸查詢是CTE最重要也是最強大的功能。
建立CTE
CTE通過關鍵字WITH建立,其模板為:
WITH CTE_name[ (column_name [,...n] ) ]
AS
( CTE_query_specification )
如果在CTE定義中提到的欄名稱是唯一的,那麼您可以不必給它們命名。不過,您同樣也可以對它們重新命名。
下面的例子應用到SQL Server 2005中的AdventureWorks樣本數據庫。這個數據庫被高度規格化,因此需要幾個連接來集合與雇員有關的信息。視圖簡化了這一操作,但也收集了所有有關雇員的信息,而您可能僅僅需要其中一部分資料。
AdventureWorks的雇員數據分布在幾個表中;而且,雇員與經理被存儲在同一個表中(HumanResources.Employee),而他們的姓名(及其它數據)則存儲在Person.Contact表中,這使得這個問題更加復雜。
首先,我們建立一個恢復雇員姓名的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 AS e
INNER JOIN Person.ContactAS c ON c.ContactID = e.ContactID
)
然後,我們可從CTE中選擇一欄或幾欄,就像它是一個標准的表或視圖。
接著我們再進一步。我們需要雇員和他們經理的姓名,於是我們使用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 AS e
INNER JOIN Person.ContactAS c ON c.ContactID = e.ContactID
)
SELECT E.FirstName + ' ' E.LastName Employee,
M.FirstName + ' ' M.LastName Manager
FROM cte_Employee AS E
LEFT OUTER JOIN cte_Employee AS M
ON E.ManagerID = M.EmployeeID
限制:不能在一個語句中建立兩個CTE。
總結
CTE是SQL Server 2005的一項強大而靈活的功能。它使得SQL Server的可讀性更強,更易於管理,降低了查詢的復雜程度。如上所述,您可以在一個SQL Server語句中多次應用CTE。