WITH common_table_expression (Transact-SQL)

指定稱為通用資料表運算式 (CTE) 的暫存具名結果集。這是從簡單查詢衍生而來，定義在單一 SELECT、INSERT、UPDATE、MERGE 或 DELETE 陳述式的執行範圍內。您也可以在 CREATE VIEW 陳述式中使用這個子句，作為用來定義 SELECT 陳述式的一部分。通用資料表運算式可以包括指向本身的參考。這稱為遞迴通用資料表運算式。

Transact-SQL 語法慣例

語法

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
        expression_name [ ( column_name [ ,...n ] ) ]
    AS
        ( CTE_query_definition )

引數

• expression_name
  這是通用資料表運算式的有效識別碼。 expression_name 與相同 WITH <common_table_expression> 子句所定義之任何其他通用資料表運算式的名稱不得相同，但 expression_name 可以與基底資料表或檢視同名。查詢中任何指向 expression_name 的參考都使用通用資料表運算式，而不是基底物件。

• column_name
  在通用資料表運算式中，指定資料行名稱。在單一 CTE 定義內，名稱不能重複。指定的資料行名稱數目必須符合 CTE_query_definition 的結果集資料行數目。只有在查詢定義提供了所有結果資料行的個別名稱時，資料行名稱清單才是選擇性的。

• CTE_query_definition
  指定其結果集擴展通用資料表運算式的 SELECT 陳述式。除了 CTE 不能建立另一個 CTE 之外，CTE_query_definition 的 SELECT 陳述式必須符合建立檢視的相同需求。如需詳細資訊，請參閱「備註」一節和＜CREATE VIEW (Transact-SQL)＞。

  如果定義了多個 CTE_query_definition，就必須由下列設定運算子來聯結查詢定義：UNION ALL、UNION、EXCEPT 或 INTERSECT。如需有關如何使用遞迴 CTE 查詢定義的詳細資訊，請參閱下面的「備註」一節和＜使用一般資料表運算式的遞迴查詢＞。

備註

建立和使用通用資料表運算式的方針

下列方針適用於非遞迴的通用資料表運算式。如需適用於遞迴通用資料表運算式的方針，請參閱下面的「定義和使用遞迴通用資料表運算式的方針」。

• CTE 之後必須接著參考部分或所有 CTE 資料行的單一 SELECT、INSERT、UPDATE、MERGE 或 DELETE 陳述式。您也可以在 CREATE VIEW 陳述式中，將 CTE 指定為用來定義檢視的 SELECT 陳述式的一部分。

• 您可以在非遞迴的 CTE 中，定義多個 CTE 查詢定義。這些定義必須由下列其中一個設定運算子所組合：UNION ALL、UNION、INTERSECT 或 EXCEPT。

• CTE 可以參考它本身，以及先前在相同 WITH 子句中所定義的 CTE。不允許向前參考。

• 不允許在 CTE 中指定多個 WITH 子句。例如，如果 CTE_query_definition 包含子查詢，這個子查詢就不能包含定義另一個 CTE 的巢狀 WITH 子句。

• 在 CTE_query_definition 中，不能使用下列子句：

  • COMPUTE 或 COMPUTE BY

  • ORDER BY (除非指定了 TOP 子句)

  • INTO

  • 含有查詢提示的 OPTION 子句

  • FOR XML

  • FOR BROWSE

• 當批次中的陳述式使用 CTE 時，在 CTE 之前的陳述式，後面必須接著分號。

• 參考 CTE 的查詢可用來定義資料指標。

• 在 CTE 中，可以參考遠端伺服器的資料表。

• 當執行 CTE 時，任何參考 CTE 的提示都可能如同在查詢中參考檢視的提示，與 CTE 存取基礎資料表時所發現的其他提示衝突。當這個情況發生時，查詢會傳回錯誤。如需詳細資訊，請參閱＜檢視解析＞。

• 當 CTE 是 UPDATE 陳述式的目標時，陳述式中所有 CTE 的參考都必須相符。例如，如果 CTE 被指派 FROM 子句中的別名，此別名就必須用於 CTE 的所有其他參考。模稜兩可的 CTE 參考可能會產生無法預期的聯結行為和不想要的查詢結果。如需詳細資訊，請參閱＜UPDATE (Transact-SQL)＞。

定義和使用遞迴通用資料表運算式的方針

下列方針適用於定義遞迴通用資料表運算式：

• 遞迴 CTE 定義必須包含至少兩個 CTE 查詢定義，錨點成員和遞迴成員各一個。您可以定義多個錨點成員和遞迴成員；不過，所有錨點成員查詢定義都必須放在第一個遞迴成員定義的前面。除非 CTE 查詢定義參考 CTE 本身，否則，它們都是錨點成員。

• 錨點成員必須由下列其中一個設定運算子所組合：UNION ALL、UNION、INTERSECT 或 EXCEPT。在最後一個錨點成員和第一個遞迴成員之間，以及在組合多個成員時，UNION ALL 是唯一允許使用的設定運算子。

• 錨點和遞迴成員中的資料行數目必須相同。

• 遞迴成員資料行的資料類型必須與錨點成員中對應資料行的資料類型相同。

• 遞迴成員的 FROM 子句只能參考 CTE expression_name 一次。

• 遞迴成員的 CTE_query_definition 不允許使用下列項目：

  • SELECT DISTINCT

  • GROUP BY

  • HAVING

  • 純量彙總

  • TOP

  • LEFT、RIGHT、OUTER JOIN (允許 INNER JOIN)

  • 子查詢

  • 適用於 CTE_query_definition 內 CTE 之遞迴參考的提示。

下列方針適用於使用遞迴通用資料表運算式：

• 遞迴 CTE 能夠傳回的所有資料行都可為 Null，不論參與的 SELECT 陳述式所傳回之資料行的 Null 屬性為何，都是如此。

• 組合不正確的遞迴 CTE 可能會造成無限迴圈。例如，如果遞迴成員查詢定義針對父資料行和子資料行傳回相同的值，就會建立無限迴圈。若要防止無限迴圈，您可以在 INSERT、UPDATE、MERGE、DELETE 或 SELECT 陳述式的 OPTION 子句中使用 MAXRECURSION 提示以及 0 和 32,767 之間的值，藉以限制特定陳述式所能使用的遞迴層級數目。這可讓您控制陳述式的執行，直到產生迴圈的程式碼問題解決為止。伺服器範圍的預設值是 100。當指定 0 時，不會套用任何限制。每個陳述式只能指定一個 MAXRECURSION 值。如需詳細資訊，請參閱＜查詢提示 (Transact-SQL)＞。

• 您不能利用包含遞迴通用資料表運算式的檢視來更新資料。

• 資料指標可以利用 CTE 在查詢中定義。CTE 是 select_statement 定義資料指標之結果集的引數。遞迴 CTE 只能使用僅限向前快轉和靜態 (快照集) 資料指標。如果在遞迴 CTE 中指定了另一種資料指標類型，就會將資料指標類型轉換成靜態。

• 在 CTE 中，可以參考遠端伺服器的資料表。如果在 CTE 遞迴成員參考遠端伺服器，便會為每個遠端資料表各建立一項多工緩衝處理，以便在本機重複存取資料表。如果它是 CTE 查詢，索引多工緩衝處理/延遲多工緩衝處理會顯示在查詢計畫中，而且將會有額外的 WITH STACK 述詞。這是確認適當遞迴的一個方式。

• SQL Server 2008 在 CTE 的遞迴部分中不允許分析和彙總函數。

範例

A. 建立簡單的通用資料表運算式

下列範例會顯示直接向 Adventure Works Cycles 之每位經理提出報告的員工人數。

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) 
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
SELECT ManagerID, DirectReports 
FROM DirReps 
ORDER BY ManagerID;
GO

B. 利用通用資料表運算式來限制計數和報告平均值

下列範例會顯示向經理提出報告的平均員工人數。

WITH DirReps (Manager, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) AS DirectReports
    FROM HumanResources.Employee
    GROUP BY ManagerID
) 
SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
FROM DirReps 
WHERE DirectReports>= 2 ;
GO

C. 重複參考通用資料表運算式

下列範例會顯示每位銷售人員的 SalesOrderHeader 資料表中，銷售訂單的總數和最近的銷售訂單日期。在執行的陳述式中，參考 CTE 兩次：一次是傳回銷售人員所選的資料行，一次是擷取銷售人員經理的類似明細。銷售人員和經理的資料都在單一資料列中傳回。

USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

D. 利用遞迴通用資料表運算式來顯示多層級的遞迴

下列範例會顯示經理及向經理提出報告的員工的階層式清單。

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports ;
GO

E. 利用遞迴通用資料表運算式來顯示兩個層級的遞迴

下列範例會顯示經理及向經理提出報告的員工。傳回的層級數目只限兩個。

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports 
WHERE EmployeeLevel <= 2 ;
GO

F. 利用遞迴通用資料表運算式來顯示階層式清單

下列範例是以 C 範例為基礎來建立的，它加入了經理和員工的姓名及其職稱。各個層級會進行縮排，更明顯地強調經理和員工的階層。

USE AdventureWorks;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
    FROM HumanResources.Employee as e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports 
ORDER BY Sort;
GO

G. 利用 MAXRECURSION 來取消陳述式

您可以利用 MAXRECURSION 來防止形式不良的遞迴 CTE 進入無限迴圈。下列範例會刻意建立無限迴圈，然後利用 MAXRECURSION 提示，將遞迴層級限制為 2。

USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

更正編碼錯誤之後，就不再需要 MAXRECURSION。下列範例會顯示更正的程式碼。

USE AdventureWorks;
GO
WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM HumanResources.Employee AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

H. 利用通用資料表運算式，在 SELECT 陳述式中選擇性地逐步執行遞迴關聯性

下列範例會顯示建立 ProductAssemblyID = 800 的自行車時，所需要之產品組件和元件的階層。

USE AdventureWorks;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom 
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
        ComponentLevel 
FROM Parts AS p
    INNER JOIN Production.Product AS pr
    ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO

I. 在 UPDATE 陳述式中使用遞迴 CTE

下列範例會將直接或間接向 ManagerID 12 提出報告的所有員工之 VacationHours 值更新為 25%。通用資料表運算式會傳回一份階層式員工清單，其中包括直接向 ManagerID 12 提出報告的員工，以及向這些員工提出報告的員工，依此類推。只會修改通用資料表運算式所傳回的資料列。

USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO

J. 使用多個錨點和遞迴成員

下列範例會利用多個錨點和遞迴成員來傳回指定人員的所有上階。它會建立一份資料表，且會插入值來建立遞迴 CTE 所傳回的家族族譜。

-- Genealogy table
IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person;
GO
CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT Person VALUES(1, 'Sue', NULL, NULL);
INSERT Person VALUES(2, 'Ed', NULL, NULL);
INSERT Person VALUES(3, 'Emma', 1, 2);
INSERT Person VALUES(4, 'Jack', 1, 2);
INSERT Person VALUES(5, 'Jane', NULL, NULL);
INSERT Person VALUES(6, 'Bonnie', 5, 4);
INSERT Person VALUES(7, 'Bill', 5, 4);
GO
-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation (ID) AS
(
-- First anchor member returns Bonnie's mother.
    SELECT Mother 
    FROM Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father 
    FROM Person
    WHERE Name = 'Bonnie'
UNION ALL
-- First recursive member returns male ancestors of the previous generation.
    SELECT Person.Father
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
UNION ALL
-- Second recursive member returns female ancestors of the previous generation.
    SELECT Person.Mother
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ID = Person.ID;
GO

變更記錄

更新的內容
在「建立和使用通用資料表運算式的方針」一節中新增一個項目，其中描述當 CTE 是 UPDATE 陳述式的目標時 CTE 的名稱需求。

請參閱

參考

CREATE VIEW (Transact-SQL)

DELETE (Transact-SQL)

EXCEPT 和 INTERSECT (Transact-SQL)

INSERT (Transact-SQL)

SELECT (Transact-SQL)

UPDATE (Transact-SQL)

MERGE (Transact-SQL)

概念

使用一般資料表運算式的遞迴查詢

使用一般資料表運算式