• 發行項

使用一般資料表運算式

一般資料表運算式 (CTE) 可視為在單一 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 陳述式之執行範圍內定義的暫存結果集。CTE 與衍生資料表類似的地方在於，它不會儲存為物件，而且其生命週期僅限於查詢期間。但是與衍生資料表不同的是，CTE 可以自我參考，而且在同一查詢中可參考多次。

CTE 可用於：

  • 建立遞迴查詢。如需詳細資訊，請參閱＜使用一般資料表運算式的遞迴查詢＞。

  • 在不需要一般用途檢視時替代檢視；亦即，您不需要在中繼資料中儲存定義。

  • 將衍生自純量子選取 (或衍生自不具決定性或具有外部存取之函數) 的資料行組成群組。

  • 在相同陳述式中多次參考結果資料表。

使用 CTE 噁提供更適合閱讀以及易於維護複雜查詢等優點。查詢可分為個別、簡單、邏輯式建置組塊。接著這些簡單組塊可用於建立更為複雜、臨時的 CTE，直到產生最後的結果集。

CTE 可以定義於使用者自訂常式，如函數、預存程序、觸發程序或檢視。

CTE 結構

CTE 是由代表 CTE 的運算式名稱、選擇性資料行清單以及定義 CTE 的查詢所組成。定義 CTE 之後，就像在 SELECT、INSERT、UPDATE 或 DELETE 陳述式中參考資料表或檢視一樣，也可以參考 CTE。CTE 也能在 CREATE VIEW 陳述式中作為定義 SELECT 陳述式的一部份。

CTE 的基本語法結構：

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

只有在查詢定義提供了所有結果資料行的個別名稱時，資料行名稱清單才是選擇性的。

執行 CTE 的陳述式如下：

SELECT <column_list>

FROM expression_name;

範例

以下範例顯示 CTE 結構的元件：運算式名稱、資料行清單和查詢。CTE 運算式 Sales_CTE 有三個資料行 (SalesPersonID、SalesOrderID, and OrderDate)，而且會為每位銷售人員定義每年的銷售訂單總數。

USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO

以下是部分結果集：

SalesPersonID TotalSales  SalesYear

------------- ----------- -----------

274           4           2001

274           20          2002

274           14          2003

274           10          2004

275           56          2001

275           139         2002

275           169         2003