WITH common_table_expression (Transact-SQL)WITH common_table_expression (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

指定稱為通用資料表運算式 (CTE) 的暫存具名結果集。Specifies a temporary named result set, known as a common table expression (CTE). 這是從簡單查詢衍生而來,定義在單一 SELECT、INSERT、UPDATE、DELETE 或 MERGE 陳述式的執行範圍內。This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. 您也可以在 CREATE VIEW 陳述式中使用這個子句,作為用來定義 SELECT 陳述式的一部分。This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. 通用資料表運算式可以包括指向本身的參考。A common table expression can include references to itself. 這稱為遞迴通用資料表運算式。This is referred to as a recursive common table expression.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

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

引數Arguments

expression_nameexpression_name
這是通用資料表運算式的有效識別碼。Is a valid identifier for the common table expression. expression_name 與相同 WITH <common_table_expression> 子句中所定義之任何其他通用資料表運算式的名稱不得相同,但 expression_name 可以與基底資料表或檢視表同名。expression_name must be different from the name of any other common table expression defined in the same WITH <common_table_expression> clause, but expression_name can be the same as the name of a base table or view. 任何指向 expression_name 的參考都是使用通用資料表運算式,而不是基底物件。Any reference to expression_name in the query uses the common table expression and not the base object.

column_namecolumn_name
在一般資料表運算式中,指定資料行名稱。Specifies a column name in the common table expression. 在單一 CTE 定義內,名稱不能重複。Duplicate names within a single CTE definition are not allowed. 指定的資料行名稱數目必須與 CTE_query_definition 的結果集資料行數目相符。The number of column names specified must match the number of columns in the result set of the CTE_query_definition. 只有在查詢定義提供了所有結果資料行的個別名稱時,資料行名稱清單才是選擇性的。The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

CTE_query_definitionCTE_query_definition
指定其結果集擴展一般資料表運算式的 SELECT 陳述式。Specifies a SELECT statement whose result set populates the common table expression. 除了 CTE 不能定義另一個 CTE 之外,CTE_query_definition 的 SELECT 陳述式必須符合建立檢視表的相同需求。The SELECT statement for CTE_query_definition must meet the same requirements as for creating a view, except a CTE cannot define another CTE. 如需詳細資訊,請參閱<備註>一節和 CREATE VIEW (Transact-SQL)For more information, see the Remarks section and CREATE VIEW (Transact-SQL).

如果定義了多個 CTE_query_definition,就必須由下列設定運算子來聯結查詢定義:UNION ALL、UNION、EXCEPT 或 INTERSECT。If more than one CTE_query_definition is defined, the query definitions must be joined by one of these set operators: UNION ALL, UNION, EXCEPT, or INTERSECT.

RemarksRemarks

建立和使用通用資料表運算式的方針Guidelines for Creating and Using Common Table Expressions

下列方針適用於非遞迴的通用資料表運算式。The following guidelines apply to nonrecursive common table expressions. 如需適用於遞迴通用資料表運算式的方針,請參閱下面的定義和使用遞迴通用資料表運算式的方針For guidelines that apply to recursive common table expressions, see Guidelines for Defining and Using Recursive Common Table Expressions that follows.

  • CTE 之後必須接著參考部分或所有 CTE 資料行的單一 SELECTINSERTUPDATEDELETE 陳述式。A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns. 您也可以在 CREATE VIEW 陳述式中,將 CTE 指定為檢視之定義 SELECT 陳述式的一部分。A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.

  • 您可以在非遞迴的 CTE 中,定義多個 CTE 查詢定義。Multiple CTE query definitions can be defined in a nonrecursive CTE. 這些定義必須透過下列其中一個設定運算子來組合:UNION ALLUNIONINTERSECTEXCEPTThe definitions must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT.

  • CTE 可以參考其本身,以及先前在相同 WITH 子句中所定義的 CTE。A CTE can reference itself and previously defined CTEs in the same WITH clause. 不允許向前參考。Forward referencing is not allowed.

  • 不允許在 CTE 中指定多個 WITH 子句。Specifying more than one WITH clause in a CTE is not allowed. 例如,如果 CTE_query_definition 包含子查詢,該子查詢就不能包含定義另一個 CTE 的巢狀 WITH 子句。For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

  • CTE_query_definition中,不能使用下列子句:The following clauses cannot be used in the CTE_query_definition:

    • ORDER BY (除非指定了 TOP 子句)ORDER BY (except when a TOP clause is specified)

    • INTO

    • 含有查詢提示的 OPTION 子句OPTION clause with query hints

    • FOR BROWSE

  • 當批次中的陳述式使用 CTE 時,在 CTE 之前的陳述式,後面必須接著分號。When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

  • 參考 CTE 的查詢可用來定義資料指標。A query referencing a CTE can be used to define a cursor.

  • 在 CTE 中,可以參考遠端伺服器的資料表。Tables on remote servers can be referenced in the CTE.

  • 當執行 CTE 時,任何參考 CTE 的提示都可能如同在查詢中參考檢視表的提示,與 CTE 存取基礎資料表時所發現的其他提示衝突。When executing a CTE, any hints that reference a CTE may conflict with other hints that are discovered when the CTE accesses its underlying tables, in the same manner as hints that reference views in queries. 當這種情況發生時,查詢會傳回錯誤:When this occurs, the query returns an error.

定義和使用遞迴通用資料表運算式的方針Guidelines for Defining and Using Recursive Common Table Expressions

下列方針適用於定義遞迴通用資料表運算式:The following guidelines apply to defining a recursive common table expression:

  • 遞迴 CTE 定義必須包含至少兩個 CTE 查詢定義,錨點成員和遞迴成員各一個。The recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. 您可以定義多個錨點成員和遞迴成員;不過,所有錨點成員查詢定義都必須放在第一個遞迴成員定義的前面。Multiple anchor members and recursive members can be defined; however, all anchor member query definitions must be put before the first recursive member definition. 除非 CTE 查詢定義參考 CTE 本身,否則,它們都是錨點成員。All CTE query definitions are anchor members unless they reference the CTE itself.

  • 錨點成員必須由下列其中一個設定運算子所組合:UNION ALL、UNION、INTERSECT 或 EXCEPT。Anchor members must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT. 在最後一個錨點成員和第一個遞迴成員之間,以及在組合多個成員時,UNION ALL 是唯一允許使用的設定運算子。UNION ALL is the only set operator allowed between the last anchor member and first recursive member, and when combining multiple recursive members.

  • 錨點和遞迴成員中的資料行數目必須相同。The number of columns in the anchor and recursive members must be the same.

  • 遞迴成員資料行的資料類型必須與錨點成員中對應資料行的資料類型相同。The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.

  • 遞迴成員的 FROM 子句只能參考 CTE expression_name一次。The FROM clause of a recursive member must refer only one time to the CTE expression_name.

  • 遞迴成員的 CTE_query_definition 中不允許使用下列項目:The following items are not allowed in the CTE_query_definition of a recursive member:

    • SELECT DISTINCT

    • GROUP BY

    • PIVOT (當資料庫相容性層級為 110 以上時。PIVOT (When the database compatibility level is 110 or higher. 請參閱 SQL Server 2016 中對於資料庫引擎的重大變更)。See Breaking Changes to Database Engine Features in SQL Server 2016.)

    • HAVING

    • 純量彙總Scalar aggregation

    • TOP

    • LEFTRIGHTOUTER JOIN (允許使用 INNER JOIN)LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed)

    • 子查詢Subqueries

    • 適用於對 CTE_query_definition 內 CTE 之遞迴參考的提示。A hint applied to a recursive reference to a CTE inside a CTE_query_definition.

下列方針適用於使用遞迴通用資料表運算式:The following guidelines apply to using a recursive common table expression:

  • 遞迴 CTE 能夠傳回的所有資料行都可為 Null,不論參與的 SELECT 陳述式所傳回之資料行 Null 屬性為何,都是如此。All columns returned by the recursive CTE are nullable regardless of the nullability of the columns returned by the participating SELECT statements.

  • 組合不正確的遞迴 CTE 可能會造成無限迴圈。An incorrectly composed recursive CTE may cause an infinite loop. 例如,若遞迴成員查詢定義對父資料行與子資料行傳回相同的值,就會建立無限迴圈。For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. 若要防止無限迴圈,您可以在 INSERTUPDATEDELETESELECT 陳述式的 OPTION 子句中使用 MAXRECURSION 提示以及 0 與 32,767 之間的值,來限制特定陳述式所能使用的遞迴層級數目。To prevent an infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement. 這可讓您控制陳述式的執行,直到產生迴圈的程式碼問題解決為止。This lets you control the execution of the statement until you resolve the code problem that is creating the loop. 伺服器範圍的預設值是 100。The server-wide default is 100. 當指定 0 時,不會套用任何限制。When 0 is specified, no limit is applied. 每個陳述式只能指定一個 MAXRECURSION 值。Only one MAXRECURSION value can be specified per statement. 如需詳細資訊,請參閱查詢提示 (Transact-SQL)For more information, see Query Hints (Transact-SQL).

  • 您不能利用包含遞迴通用資料表運算式的檢視來更新資料。A view that contains a recursive common table expression cannot be used to update data.

  • 資料指標可以利用 CTE 在查詢中定義。Cursors may be defined on queries using CTEs. CTE 是定義資料指標結果集的 select_statement 引數。The CTE is the select_statement argument that defines the result set of the cursor. 遞迴 CTE 只能使用僅限向前快轉和靜態 (快照集) 資料指標。Only fast forward-only and static (snapshot) cursors are allowed for recursive CTEs. 如果在遞迴 CTE 中指定了另一種資料指標類型,就會將資料指標類型轉換成靜態。If another cursor type is specified in a recursive CTE, the cursor type is converted to static.

  • 在 CTE 中,可以參考遠端伺服器的資料表。Tables on remote servers may be referenced in the CTE. 如果在 CTE 遞迴成員參考遠端伺服器,便會為每個遠端資料表各建立一項多工緩衝處理,以便在本機重複存取資料表。If the remote server is referenced in the recursive member of the CTE, a spool is created for each remote table so the tables can be repeatedly accessed locally. 如果它是 CTE 查詢,索引多工緩衝處理/延遲多工緩衝處理會顯示在查詢計畫中,且將會有額外的 WITH STACK 述詞。If it is a CTE query, Index Spool/Lazy Spools is displayed in the query plan and will have the additional WITH STACK predicate. 這是確認適當遞迴的一種方式。This is one way to confirm proper recursion.

  • CTE 遞迴部分中的分析和彙總函式會套用至目前遞迴層級的集合,而不會套用至 CTE 的集合。Analytic and aggregate functions in the recursive part of the CTE are applied to the set for the current recursion level and not to the set for the CTE. ROW_NUMBER 之類的函式只會針對目前遞迴層級傳遞給它們的資料子集運作,而不會針對傳遞給 CTE 遞迴部分的整個資料集運作。Functions like ROW_NUMBER operate only on the subset of data passed to them by the current recursion level and not the entire set of data passed to the recursive part of the CTE. 如需詳細資訊,請參閱下面的範例「K. 在遞迴 CTE 中使用分析函數」。For more information, see example K. Using analytical functions in a recursive CTE that follows.

SQL 資料倉儲SQL Data Warehouse平行處理資料倉儲Parallel Data Warehouse中通用資料表運算式的功能和限制Features and Limitations of Common Table Expressions in SQL 資料倉儲SQL Data Warehouse and 平行處理資料倉儲Parallel Data Warehouse

SQL 資料倉儲SQL Data Warehouse平行處理資料倉儲Parallel Data Warehouse中目前的 CTE 實作具有下列功能和限制:The current implementation of CTEs in SQL 資料倉儲SQL Data Warehouse and 平行處理資料倉儲Parallel Data Warehouse have the following features and limitations:

  • 可以在 SELECT 陳述式中指定 CTE。A CTE can be specified in a SELECT statement.

  • 可以在 CREATE VIEW 陳述式中指定 CTE。A CTE can be specified in a CREATE VIEW statement.

  • 可以在 CREATE TABLE AS SELECT (CTAS) 陳述式中指定 CTE。A CTE can be specified in a CREATE TABLE AS SELECT (CTAS) statement.

  • 可以在 CREATE REMOTE TABLE AS SELECT (CRTAS) 陳述式中指定 CTE。A CTE can be specified in a CREATE REMOTE TABLE AS SELECT (CRTAS) statement.

  • 可以在 CREATE EXTERNAL TABLE AS SELECT (CETAS) 陳述式中指定 CTE。A CTE can be specified in a CREATE EXTERNAL TABLE AS SELECT (CETAS) statement.

  • 可以從 CTE 參考遠端資料表。A remote table can be referenced from a CTE.

  • 可以從 CTE 參考外部資料表。An external table can be referenced from a CTE.

  • 可以在 CTE 中定義多個 CTE 查詢定義。Multiple CTE query definitions can be defined in a CTE.

  • CTE 後面必須接著單一 SELECT 陳述式。A CTE must be followed by a single SELECT statement. 不支援 INSERTUPDATEDELETEMERGE 陳述式。INSERT, UPDATE, DELETE, and MERGE statements are not supported.

  • 不支援包含自我參考的通用資料表運算式 (遞迴通用資料表運算式)。A common table expression that includes references to itself (a recursive common table expression) is not supported.

  • 不允許在 CTE 中指定多個 WITH 子句。Specifying more than one WITH clause in a CTE is not allowed. 例如,如果 CTE 查詢定義包含子查詢,該子查詢就不能包含定義另一個 CTE 的巢狀 WITH 子句。For example, if a CTE query definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

  • 除非已指定 TOP 子句,否則不能在 CTE_query_definition 中使用 ORDER BY 子句。An ORDER BY clause cannot be used in the CTE_query_definition, except when a TOP clause is specified.

  • 當批次中的陳述式使用 CTE 時,在 CTE 之前的陳述式,後面必須接著分號。When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

  • sp_prepare 所準備的陳述式中使用 CTE 時,CTE 的行為會與 PDW 中的其他 SELECT 陳述式相同。When used in statements prepared by sp_prepare, CTEs will behave the same way as other SELECT statements in PDW. 不過,如果是在 sp_prepare 所準備的 CETAS 中使用 CTE,則由於受到針對 sp_prepare 實作繫結之方式的影響,CTE 的行為可能會與 SQL ServerSQL Server 及其他 PDW 陳述式不同。However, if CTEs are used as part of CETAS prepared by sp_prepare, the behavior can defer from SQL ServerSQL Server and other PDW statements because of the way binding is implemented for sp_prepare. 如果參考 CTE 的 SELECT 使用不存在於 CTE 中的錯誤資料行,sp_prepare 將不會偵測錯誤就逕行通過,但改為在 sp_execute 期間擲回該錯誤。If SELECT that references CTE is using a wrong column that does not exist in CTE, the sp_prepare will pass without detecting the error, but the error will be thrown during sp_execute instead.

範例Examples

A.A. 建立簡單的通用資料表運算式Creating a simple common table expression

下列範例顯示在 Adventure Works CyclesAdventure Works Cycles 中每個業務人員每年的銷售訂單總數。The following example shows the total number of sales orders per year for each sales representative at Adventure Works CyclesAdventure Works Cycles.

-- 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;  

B.B. 利用通用資料表運算式來限制計數和報告平均值Using a common table expression to limit counts and report averages

下列範例顯示業務人員所有年度的平均銷售訂單數目。The following example shows the average number of sales orders for all years for the sales representatives.

WITH Sales_CTE (SalesPersonID, NumberOfOrders)  
AS  
(  
    SELECT SalesPersonID, COUNT(*)  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
    GROUP BY SalesPersonID  
)  
SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"  
FROM Sales_CTE;  

C.C. 在單一查詢中使用多個 CTE 定義Using multiple CTE definitions in a single query

下列範例會示範如何在單一查詢中定義一個以上的 CTE。The following example shows how to define more than one CTE in a single query. 請注意,逗號會用來分隔 CTE 查詢的定義。Notice that a comma is used to separate the CTE query definitions. 以貨幣格式顯示貨幣金額的 FORMAT 函數會在 SQL Server 2012 和更新版本中提供。The FORMAT function, used to display the monetary amounts in a currency format, is available in SQL Server 2012 and higher.

WITH Sales_CTE (SalesPersonID, TotalSales, SalesYear)  
AS  
-- Define the first CTE query.  
(  
    SELECT SalesPersonID, SUM(TotalDue) AS TotalSales, YEAR(OrderDate) AS SalesYear  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
       GROUP BY SalesPersonID, YEAR(OrderDate)  
  
)  
,   -- Use a comma to separate multiple CTE definitions.  
  
-- Define the second CTE query, which returns sales quota data by year for each sales person.  
Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear)  
AS  
(  
       SELECT BusinessEntityID, SUM(SalesQuota)AS SalesQuota, YEAR(QuotaDate) AS SalesQuotaYear  
       FROM Sales.SalesPersonQuotaHistory  
       GROUP BY BusinessEntityID, YEAR(QuotaDate)  
)  
  
-- Define the outer query by referencing columns from both CTEs.  
SELECT SalesPersonID  
  , SalesYear  
  , FORMAT(TotalSales,'C','en-us') AS TotalSales  
  , SalesQuotaYear  
  , FORMAT (SalesQuota,'C','en-us') AS SalesQuota  
  , FORMAT (TotalSales -SalesQuota, 'C','en-us') AS Amt_Above_or_Below_Quota  
FROM Sales_CTE  
JOIN Sales_Quota_CTE ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID  
                    AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear  
ORDER BY SalesPersonID, SalesYear;    

以下為部分結果集。Here is a partial result set.

SalesPersonID SalesYear   TotalSales    SalesQuotaYear SalesQuota  Amt_Above_or_Below_Quota  
------------- ---------   -----------   -------------- ---------- ----------------------------------   
274           2005        $32,567.92    2005           $35,000.00  ($2,432.08)  
274           2006        $406,620.07   2006           $455,000.00 ($48,379.93)  
274           2007        $515,622.91   2007           $544,000.00 ($28,377.09)  
274           2008        $281,123.55   2008           $271,000.00  $10,123.55  

D.D. 利用遞迴通用資料表運算式來顯示多層級的遞迴Using a recursive common table expression to display multiple levels of recursion

下列範例會顯示經理及向經理提出報告的員工的階層式清單。The following example shows the hierarchical list of managers and the employees who report to them. 此範例會開始建立並擴展 dbo.MyEmployees 資料表。The example begins by creating and populating the dbo.MyEmployees table.

-- Create an Employee table.  
CREATE TABLE dbo.MyEmployees  
(  
EmployeeID smallint NOT NULL,  
FirstName nvarchar(30)  NOT NULL,  
LastName  nvarchar(40) NOT NULL,  
Title nvarchar(50) NOT NULL,  
DeptID smallint NOT NULL,  
ManagerID int NULL,  
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)   
);  
-- Populate the table with values.  
INSERT INTO dbo.MyEmployees VALUES   
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)  
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)  
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)  
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)  
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)  
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)  
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)  
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)  
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);  
USE AdventureWorks2012;  
GO  
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS   
(  
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel  
    FROM dbo.MyEmployees   
    WHERE ManagerID IS NULL  
    UNION ALL  
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1  
    FROM dbo.MyEmployees AS e  
        INNER JOIN DirectReports AS d  
        ON e.ManagerID = d.EmployeeID   
)  
SELECT ManagerID, EmployeeID, Title, EmployeeLevel   
FROM DirectReports  
ORDER BY ManagerID;   

E.E. 利用遞迴通用資料表運算式來顯示兩個層級的遞迴Using a recursive common table expression to display two levels of recursion

下列範例會顯示經理及向經理提出報告的員工。The following example shows managers and the employees reporting to them. 傳回的層級數目只限兩個。The number of levels returned is limited to two.

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

F.F. 利用遞迴通用資料表運算式來顯示階層式清單Using a recursive common table expression to display a hierarchical list

下列範例是以範例 D 為基礎所建立,它加入了經理和員工的姓名及其職稱。The following example builds on Example D by adding the names of the manager and employees, and their respective titles. 各個層級會進行縮排,更明顯地強調經理和員工的階層。The hierarchy of managers and employees is additionally emphasized by indenting each level.

USE AdventureWorks2012;  
GO  
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)  
AS (SELECT CONVERT(varchar(255), e.FirstName + ' ' + e.LastName),  
        e.Title,  
        e.EmployeeID,  
        1,  
        CONVERT(varchar(255), e.FirstName + ' ' + e.LastName)  
    FROM dbo.MyEmployees AS e  
    WHERE e.ManagerID IS NULL  
    UNION ALL  
    SELECT CONVERT(varchar(255), REPLICATE ('|    ' , EmployeeLevel) +  
        e.FirstName + ' ' + e.LastName),  
        e.Title,  
        e.EmployeeID,  
        EmployeeLevel + 1,  
        CONVERT (varchar(255), RTRIM(Sort) + '|    ' + FirstName + ' ' +   
                 LastName)  
    FROM dbo.MyEmployees AS e  
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID  
    )  
SELECT EmployeeID, Name, Title, EmployeeLevel  
FROM DirectReports   
ORDER BY Sort;  

G.G. 利用 MAXRECURSION 來取消陳述式Using MAXRECURSION to cancel a statement

您可以利用 MAXRECURSION 來防止形式不良的遞迴 CTE 進入無限迴圈。MAXRECURSION can be used to prevent a poorly formed recursive CTE from entering into an infinite loop. 下列範例會刻意建立無限迴圈,然後利用 MAXRECURSION 提示,將遞迴層級限制為 2。The following example intentionally creates an infinite loop and uses the MAXRECURSION hint to limit the number of recursion levels to two.

USE AdventureWorks2012;  
GO  
--Creates an infinite loop  
WITH cte (EmployeeID, ManagerID, Title) as  
(  
    SELECT EmployeeID, ManagerID, Title  
    FROM dbo.MyEmployees  
    WHERE ManagerID IS NOT NULL  
  UNION ALL  
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title  
    FROM cte   
    JOIN  dbo.MyEmployees 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);  

更正編碼錯誤之後,就不再需要 MAXRECURSION。After the coding error is corrected, MAXRECURSION is no longer required. 下列範例會顯示更正的程式碼。The following example shows the corrected code.

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

H.H. 利用通用資料表運算式,在 SELECT 陳述式中選擇性地逐步執行遞迴關聯性Using a common table expression to selectively step through a recursive relationship in a SELECT statement

下列範例會顯示建立 ProductAssemblyID = 800 的自行車時,所需要之產品組件和元件的階層。The following example shows the hierarchy of product assemblies and components that are required to build the bicycle for ProductAssemblyID = 800.

USE AdventureWorks2012;  
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;  

I.I. 在 UPDATE 陳述式中使用遞迴 CTEUsing a recursive CTE in an UPDATE statement

下列範例會為用來建置產品 'Road-550-W Yellow, 44' (ProductAssemblyID``800) 的所有組件更新 PerAssemblyQty 值。The following example updates the PerAssemblyQty value for all parts that are used to build the product 'Road-550-W Yellow, 44' (ProductAssemblyID``800). 通用資料表運算式會傳回一份階層式清單,其中包含用來建立 ProductAssemblyID 800 的組件、用來建立這些組件的元件等等。The common table expression returns a hierarchical list of parts that are used to build ProductAssemblyID 800 and the components that are used to create those parts, and so on. 只會修改通用資料表運算式所傳回的資料列。Only the rows returned by the common table expression are modified.

USE AdventureWorks2012;  
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  
)  
UPDATE Production.BillOfMaterials  
SET PerAssemblyQty = c.PerAssemblyQty * 2  
FROM Production.BillOfMaterials AS c  
JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID  
WHERE d.ComponentLevel = 0;  

J.J. 使用多個錨點和遞迴成員Using multiple anchor and recursive members

下列範例會利用多個錨點和遞迴成員來傳回指定人員的所有上階。The following example uses multiple anchor and recursive members to return all the ancestors of a specified person. 它會建立一份資料表,且會插入值來建立遞迴 CTE 所傳回的家族族譜。A table is created and values inserted to establish the family genealogy returned by the recursive CTE.

-- Genealogy table  
IF OBJECT_ID('dbo.Person','U') IS NOT NULL DROP TABLE dbo.Person;  
GO  
CREATE TABLE dbo.Person(ID int, Name varchar(30), Mother int, Father int);  
GO  
INSERT dbo.Person   
VALUES(1, 'Sue', NULL, NULL)  
      ,(2, 'Ed', NULL, NULL)  
      ,(3, 'Emma', 1, 2)  
      ,(4, 'Jack', 1, 2)  
      ,(5, 'Jane', NULL, NULL)  
      ,(6, 'Bonnie', 5, 4)  
      ,(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 dbo.Person  
    WHERE Name = 'Bonnie'  
UNION  
-- Second anchor member returns Bonnie's father.  
    SELECT Father   
    FROM dbo.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, dbo.Person  
    WHERE Generation.ID=Person.ID  
)  
SELECT Person.ID, Person.Name, Person.Mother, Person.Father  
FROM Generation, dbo.Person  
WHERE Generation.ID = Person.ID;  
GO  

K.K. 在遞迴 CTE 中使用分析函數Using analytical functions in a recursive CTE

下列範例顯示在 CTE 遞迴部分中使用分析或彙總函式時可能會發生的錯誤。The following example shows a pitfall that can occur when using an analytical or aggregate function in the recursive part of a CTE.

DECLARE @t1 TABLE (itmID int, itmIDComp int);  
INSERT @t1 VALUES (1,10), (2,10);   
  
DECLARE @t2 TABLE (itmID int, itmIDComp int);   
INSERT @t2 VALUES (3,10), (4,10);   
  
WITH vw AS  
 (  
    SELECT itmIDComp, itmID  
    FROM @t1  
  
    UNION ALL  
  
    SELECT itmIDComp, itmID  
    FROM @t2  
)   
,r AS  
 (  
    SELECT t.itmID AS itmIDComp  
           , NULL AS itmID  
           ,CAST(0 AS bigint) AS N  
           ,1 AS Lvl  
    FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t (itmID)   
  
UNION ALL  
  
SELECT t.itmIDComp  
    , t.itmID  
    , ROW_NUMBER() OVER(PARTITION BY t.itmIDComp ORDER BY t.itmIDComp, t.itmID) AS N  
    , Lvl + 1  
FROM r   
    JOIN vw AS t ON t.itmID = r.itmIDComp  
)   
  
SELECT Lvl, N FROM r;  

下列結果是此查詢的預期結果。The following results are the expected results for the query.

Lvl  N  
1    0  
1    0  
1    0  
1    0  
2    4  
2    3  
2    2  
2    1  

下列結果是此查詢的實際結果。The following results are the actual results for the query.

Lvl  N  
1    0  
1    0  
1    0  
1    0  
2    1  
2    1  
2    1  
2    1  

N 會針對 CTE 遞迴部分的每個行程傳回 1,因為只有該遞迴層級的資料子集會傳遞給 ROWNUMBERN returns 1 for each pass of the recursive part of the CTE because only the subset of data for that recursion level is passed to ROWNUMBER. 對於此查詢遞迴部分的每個反覆運算,只有一個資料列會傳遞給 ROWNUMBERFor each of the iterations of the recursive part of the query, only one row is passed to ROWNUMBER.

範例:SQL 資料倉儲SQL Data Warehouse平行處理資料倉儲Parallel Data WarehouseExamples: SQL 資料倉儲SQL Data Warehouse and 平行處理資料倉儲Parallel Data Warehouse

L.L. 在 CTAS 陳述式內使用通用資料表運算式Using a common table expression within a CTAS statement

下列範例會建立一個新的資料表,其中包含 Adventure Works CyclesAdventure Works Cycles 每個業務代表的每年銷售訂單總數。The following example creates a new table containing the total number of sales orders per year for each sales representative at Adventure Works CyclesAdventure Works Cycles.

USE AdventureWorks2012;  
GO   
CREATE TABLE SalesOrdersPerYear  
WITH  
(  
    DISTRIBUTION = HASH(SalesPersonID)  
)  
AS  
    -- 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  

M.M. 在 CETAS 陳述式內使用通用資料表運算式Using a common table expression within a CETAS statement

下列範例會建立一個新的外部資料表,其中包含 Adventure Works CyclesAdventure Works Cycles 每個業務代表的每年銷售訂單總數。The following example creates a new external table containing the total number of sales orders per year for each sales representative at Adventure Works CyclesAdventure Works Cycles.

USE AdventureWorks2012;  
GO    
CREATE EXTERNAL TABLE SalesOrdersPerYear  
WITH  
(  
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:5000/files/Customer',  
    FORMAT_OPTIONS ( FIELD_TERMINATOR = '|' )   
)  
AS  
    -- 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  

N.N. 在陳述式中使用多個以逗號分隔的 CTEUsing multiple comma separated CTEs in a statement

下列範例示範如何在單一陳述式中包含兩個 CTE。The following example demonstrates including two CTEs in a single statement. CTE 不能位於巢狀結構中 (不可遞迴)。The CTEs cannot be nested (no recursion).

WITH   
 CountDate (TotalCount, TableName) AS  
    (  
     SELECT COUNT(datekey), 'DimDate' FROM DimDate  
    ) ,  
 CountCustomer (TotalAvg, TableName) AS  
    (  
     SELECT COUNT(CustomerKey), 'DimCustomer' FROM DimCustomer  
    )  
SELECT TableName, TotalCount FROM CountDate  
UNION ALL  
SELECT TableName, TotalAvg FROM CountCustomer;  

另請參閱See Also

CREATE VIEW (Transact-SQL) CREATE VIEW (Transact-SQL)
DELETE (Transact-SQL) DELETE (Transact-SQL)
EXCEPT 和 INTERSECT (Transact-SQL) EXCEPT and INTERSECT (Transact-SQL)
INSERT (Transact-SQL) INSERT (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
UPDATE (Transact-SQL)UPDATE (Transact-SQL)