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

适用于: 是SQL Server(从 2008 开始)是Azure SQL 数据库是Azure SQL 数据仓库是并行数据仓库APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse 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 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 用在属于批处理的一部分的语句中,那么在它之前的语句必须以分号结尾。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 JOINLEFT, 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:

  • 无论参与的 SELECT 语句返回的列的为 Null 性如何,递归 CTE 返回的全部列都可以为空。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. 可以使用 MAXRECURSION 提示以及在 INSERTUPDATEDELETESELECT 语句的 OPTION 子句中的一个 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 查询,Index Spool/Lazy Spool 则显示在查询计划中,并具有额外的 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.

  • ORDER BY 子句不能用于 CTE_query_definition,指定了 TOP 子句的情况除外。An ORDER BY clause cannot be used in the CTE_query_definition, except when a TOP clause is specified.

  • 如果将 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 用于 sp_prepare 准备的语句中,则其与 PDW 中其他 SELECT 语句的行为相同。When used in statements prepared by sp_prepare, CTEs will behave the same way as other SELECT statements in PDW. 但是,如果将 CTE 用作由 sp_prepare 准备的 CETAS 的一部分,则由于为 sp_prepare 实现绑定时所使用的方式,其行为可能与 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. SQL Server 2012 和更高版本中提供 FORMAT 函数,用于以货币格式显示货币金额。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 提示将递归级别限制为两级。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,这是因为只向 ROWNUMBER 传递了该递归级别的数据子集。N 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. 对于查询递归部分的每次迭代,只向 ROWNUMBER 传递了一行。For 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)