程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL Server2005數據庫查詢中使用CTE

SQL Server2005數據庫查詢中使用CTE

編輯:關於SqlServer

通用表表達式(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。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved