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

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure simSQL Data Warehouse do Azure simParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Especifica um conjunto de resultados nomeado temporário, conhecido como uma CTE (expressão de tabela comum).Specifies a temporary named result set, known as a common table expression (CTE). Ela é derivada de uma consulta simples e definida no escopo de execução de uma única instrução SELECT, INSERT, UPDATE, DELETE ou MERGE.This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. Esta cláusula também pode ser usada em uma instrução CREATE VIEW como parte da instrução SELECT que a define.This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. Uma expressão de tabela comum pode incluir referências a si mesma.A common table expression can include references to itself. É o que chamamos de expressão de tabela comum recursiva.This is referred to as a recursive common table expression.

Ícone de link do tópico Convenções de sintaxe de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxeSyntax

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

ArgumentosArguments

expression_nameexpression_name
É um identificador válido para a expressão de tabela comum.Is a valid identifier for the common table expression. expression_name deve ser diferente do nome de qualquer outra expressão de tabela comum definida na mesma cláusula WITH <common_table_expression>, mas expression_name pode ser o mesmo que o nome de uma tabela base ou exibição.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. Qualquer referência a expression_name na consulta usa a expressão de tabela comum, e não o objeto base.Any reference to expression_name in the query uses the common table expression and not the base object.

column_namecolumn_name
Especifica um nome de coluna na expressão de tabela comum.Specifies a column name in the common table expression. Não são permitidos nomes duplicados em uma única definição de CTE.Duplicate names within a single CTE definition are not allowed. O número de nomes de coluna especificado deve corresponder ao número de colunas no conjunto de resultados da CTE_query_definition.The number of column names specified must match the number of columns in the result set of the CTE_query_definition. A lista de nomes de colunas será opcional somente se forem fornecidos nomes distintos para todas as colunas resultantes na definição da consulta.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
Especifica uma instrução SELECT cujo conjunto de resultados popula a expressão de tabela comum.Specifies a SELECT statement whose result set populates the common table expression. A instrução SELECT de CTE_query_definition deve atender aos mesmos requisitos da criação de uma exibição, com a exceção de que uma CTE não pode definir outra CTE.The SELECT statement for CTE_query_definition must meet the same requirements as for creating a view, except a CTE cannot define another CTE. Para obter mais informações, consulte a seção Comentários e CREATE VIEW (Transact-SQL).For more information, see the Remarks section and CREATE VIEW (Transact-SQL).

Se mais de uma CTE_query_definition for definida, as definições de consulta poderão ser unidas por um destes dois conjuntos de operadores: UNION ALL, UNION, EXCEPT ou 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

Diretrizes para criar e usar expressões de tabela comunsGuidelines for Creating and Using Common Table Expressions

As diretrizes a seguir se aplicam a expressões de tabela comuns não recursivas.The following guidelines apply to nonrecursive common table expressions. Para verificar as diretrizes relacionadas a expressões de tabela comuns recursivas, confira Guidelines for Defining and Using Recursive Common Table Expressions (Diretrizes para definir e usar expressões de tabela comuns recursivas) a seguir.For guidelines that apply to recursive common table expressions, see Guidelines for Defining and Using Recursive Common Table Expressions that follows.

  • Uma CTE deve ser seguida por uma única instrução SELECT, INSERT, UPDATE ou DELETE que referencia algumas ou todas as colunas da CTE.A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns. Uma CTE também pode ser especificada em uma instrução CREATE VIEW como parte da definição da instrução SELECT da exibição.A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.

  • É possível ter várias definições de consulta CTE em uma CTE não recursiva.Multiple CTE query definitions can be defined in a nonrecursive CTE. As definições devem ser combinadas por um destes operadores de conjunto: UNION ALL, UNION, INTERSECT ou EXCEPT.The definitions must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT.

  • Uma CTE pode fazer referência a si mesma e a CTEs definidas anteriormente na mesma cláusula WITH.A CTE can reference itself and previously defined CTEs in the same WITH clause. Não é permitido referência antecipada.Forward referencing is not allowed.

  • Não é permitida a especificação de mais de uma cláusula WITH em uma CTE.Specifying more than one WITH clause in a CTE is not allowed. Por exemplo, se uma CTE_query_definition contiver uma subconsulta, essa subconsulta não poderá conter uma cláusula WITH aninhada que define outra CTE.For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

  • As seguintes cláusulas não podem ser usadas na CTE_query_definition:The following clauses cannot be used in the CTE_query_definition:

    • ORDER BY (exceto quando uma cláusula TOP for especificada)ORDER BY (except when a TOP clause is specified)

    • INTO

    • Cláusula OPTION com dicas de consultaOPTION clause with query hints

    • FOR BROWSE

  • Quando uma CTE for usada em uma instrução que faça parte de um lote, a instrução anterior a ela deverá ser seguida por um ponto-e-vírgula.When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

  • Uma consulta que faça referência a uma CTE pode ser usada para definir um cursor.A query referencing a CTE can be used to define a cursor.

  • As tabelas em servidores remotos podem ser referenciadas na CTE.Tables on remote servers can be referenced in the CTE.

  • Ao executar uma CTE, quaisquer dicas que façam referência a uma CTE podem entrar em conflito com outras dicas que forem descobertas quando a CTE acessar suas tabelas subjacentes, da mesma maneira como as dicas que fazem referência a exibições em consultas.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. Quando isso ocorre, a consulta retorna um erro.When this occurs, the query returns an error.

Diretrizes para definir e usar expressões de tabela comuns recursivasGuidelines for Defining and Using Recursive Common Table Expressions

As seguintes diretrizes aplicam-se à definição de uma expressão de tabela comum recursiva:The following guidelines apply to defining a recursive common table expression:

  • A definição da CTE recursiva deve conter pelo menos duas definições de consulta de CTE, um membro de ancoragem e um membro recursivo.The recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. É possível definir vários membros de ancoragem e membros recursivos; entretanto, todas as definições de consulta de membro de ancoragem devem ser colocadas antes da primeira definição de membro recursivo.Multiple anchor members and recursive members can be defined; however, all anchor member query definitions must be put before the first recursive member definition. Todas as definições de consulta de CTE são membros de ancoragem, a menos que façam referência à própria CTE.All CTE query definitions are anchor members unless they reference the CTE itself.

  • Os membros de ancoragem devem ser combinados por um destes operadores de conjunto: UNION ALL, UNION, INTERSECT ou EXCEPT.Anchor members must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT. UNION ALL é o único operador de conjunto permitido entre o último membro de ancoragem e o primeiro membro recursivo e ao combinar vários membros recursivos.UNION ALL is the only set operator allowed between the last anchor member and first recursive member, and when combining multiple recursive members.

  • O número de colunas nos membros de ancoragem e recursivos deve ser o mesmo.The number of columns in the anchor and recursive members must be the same.

  • O tipo de dados de uma coluna no membro recursivo deve ser o mesmo que o tipo de dados da coluna correspondente no membro de ancoragem.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.

  • A cláusula FROM de um membro recursivo deve referenciar apenas uma vez o expression_name da CTE.The FROM clause of a recursive member must refer only one time to the CTE expression_name.

  • Os seguintes itens não são permitidos na CTE_query_definition de um membro recursivo:The following items are not allowed in the CTE_query_definition of a recursive member:

As seguintes diretrizes aplicam-se ao uso de uma expressão de tabela comum recursiva:The following guidelines apply to using a recursive common table expression:

  • Todas as colunas retornadas pela CTE recursiva aceitam valores nulos, independentemente da possibilidade de nulidade das colunas retornadas pelas instruções SELECT participantes.All columns returned by the recursive CTE are nullable regardless of the nullability of the columns returned by the participating SELECT statements.

  • Uma CTE recursiva incorretamente composta pode causar um loop infinito.An incorrectly composed recursive CTE may cause an infinite loop. Por exemplo, se a definição de consulta do membro recursivo retornar os mesmos valores para as colunas pai e filho, um loop infinito será criado.For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. Para prevenir um loop infinito, é possível limitar o número de níveis de recursão permitidos para uma instrução específica, usando a dica MAXRECURSION e um valor entre 0 e 32.767 na cláusula OPTION da instrução INSERT, UPDATE, DELETE ou SELECT.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. Isso permite controlar a execução da instrução até que você resolva o problema de código que está criando o loop.This lets you control the execution of the statement until you resolve the code problem that is creating the loop. O padrão para todo o servidor é 100.The server-wide default is 100. Quando 0 é especificado, nenhum limite é aplicado.When 0 is specified, no limit is applied. Apenas um valor MAXRECURSION pode ser especificado por instrução.Only one MAXRECURSION value can be specified per statement. Para obter mais informações, veja Dicas de consulta (Transact-SQL).For more information, see Query Hints (Transact-SQL).

  • Uma exibição que contém uma expressão de tabela comum recursiva não pode ser usada para atualizar dados.A view that contains a recursive common table expression cannot be used to update data.

  • É possível definir cursores em consultas usando CTEs.Cursors may be defined on queries using CTEs. A CTE é o argumento select_statement que define o conjunto de resultados do cursor.The CTE is the select_statement argument that defines the result set of the cursor. Apenas cursores de somente avanço rápido e estáticos (instantâneos) são permitidos para CTEs recursivas.Only fast forward-only and static (snapshot) cursors are allowed for recursive CTEs. Se outro tipo de cursor for especificado em uma CTE recursiva, o tipo de cursor será convertido em estático.If another cursor type is specified in a recursive CTE, the cursor type is converted to static.

  • As tabelas em servidores remotos podem ser referenciadas na CTE.Tables on remote servers may be referenced in the CTE. Se o servidor remoto for referenciado no membro recursivo da CTE, um spool será criado para cada tabela remota, de maneira que as tabelas possam ser acessadas localmente repetidamente.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. Se essa for uma consulta CTE, Index Spool/Lazy Spools será exibido no plano de consulta e terá o predicado adicional 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. Essa é uma maneira de confirmar a recursão correta.This is one way to confirm proper recursion.

  • Funções analíticas e de agregação na parte recursiva da CTE são aplicadas para definir o nível de recursão atual e não para a definição da 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. Funções como ROW_NUMBER funcionam apenas no subconjunto de dados passado para elas pelo nível de recursão atual e não no conjunto inteiro de dados passados para a parte recursiva da 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. Para obter mais informações, veja o exemplo K. Usando funções analíticas em uma CTE recursiva a seguir.For more information, see example K. Using analytical functions in a recursive CTE that follows.

Recursos e limitações de Expressões de Tabela Comum no SQL Data WarehouseSQL Data Warehouse e Parallel Data WarehouseParallel Data WarehouseFeatures and Limitations of Common Table Expressions in SQL Data WarehouseSQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

A implementação atual das CTEs no SQL Data WarehouseSQL Data Warehouse e Parallel Data WarehouseParallel Data Warehouse apresenta os seguintes recursos e limitações:The current implementation of CTEs in SQL Data WarehouseSQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse have the following features and limitations:

  • Uma CTE pode ser especificada em uma instrução SELECT.A CTE can be specified in a SELECT statement.

  • Uma CTE pode ser especificada em uma instrução CREATE VIEW.A CTE can be specified in a CREATE VIEW statement.

  • Uma CTE pode ser especificada em uma instrução CREATE TABLE AS SELECT (CTAS).A CTE can be specified in a CREATE TABLE AS SELECT (CTAS) statement.

  • Uma CTE pode ser especificada em uma instrução CREATE REMOTE TABLE AS SELECT (CRTAS).A CTE can be specified in a CREATE REMOTE TABLE AS SELECT (CRTAS) statement.

  • Uma CTE pode ser especificada em uma instrução CREATE EXTERNAL TABLE AS SELECT (CETAS).A CTE can be specified in a CREATE EXTERNAL TABLE AS SELECT (CETAS) statement.

  • Uma tabela remota pode ser referenciada em uma CTE.A remote table can be referenced from a CTE.

  • Uma tabela externa pode ser referenciada em uma CTE.An external table can be referenced from a CTE.

  • Várias definições de consulta CTE podem ser definidas em uma CTE.Multiple CTE query definitions can be defined in a CTE.

  • Uma CTE deve ser seguida por uma única instrução SELECT.A CTE must be followed by a single SELECT statement. Não há suporte para as instruções INSERT, UPDATE, DELETE e MERGE.INSERT, UPDATE, DELETE, and MERGE statements are not supported.

  • Não há suporte para uma expressão de tabela comum que inclui referências a si mesma (uma expressão de tabela comum recursiva).A common table expression that includes references to itself (a recursive common table expression) is not supported.

  • Não é permitida a especificação de mais de uma cláusula WITH em uma CTE.Specifying more than one WITH clause in a CTE is not allowed. Por exemplo, se uma definição de consulta CTE contiver uma subconsulta, essa subconsulta não poderá conter uma cláusula WITH aninhada que define outra CTE.For example, if a CTE query definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

  • Uma cláusula ORDER BY não pode ser usada na CTE_query_definition, exceto quando uma cláusula TOP é especificada.An ORDER BY clause cannot be used in the CTE_query_definition, except when a TOP clause is specified.

  • Quando uma CTE for usada em uma instrução que faça parte de um lote, a instrução anterior a ela deverá ser seguida por um ponto-e-vírgula.When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

  • Quando usadas em instruções preparadas por sp_prepare, CTEs se comportarão da mesma maneira que outras instruções SELECT no PDW.When used in statements prepared by sp_prepare, CTEs will behave the same way as other SELECT statements in PDW. No entanto, se as CTEs são usadas como parte da instrução CETAS preparada por sp_prepare, o comportamento pode ser adiado de SQL ServerSQL Server e outras instruções do PDW, devido à maneira como a associação é implementada para sp_prepare.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. Se SELECT que referencia a CTE estiver usando uma coluna incorreta que não existe na CTE, o sp_prepare será passado sem detectar o erro, mas o erro será gerado durante 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.

ExemplosExamples

A.A. Criando uma expressão de tabela comum simplesCreating a simple common table expression

O exemplo a seguir mostra o número total de pedidos de vendas por ano para cada representante de vendas na Ciclos da Adventure WorksAdventure Works Cycles.The following example shows the total number of sales orders per year for each sales representative at Ciclos da Adventure WorksAdventure 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. Usando uma expressão de tabela comum para limitar contagens e médias de relatórioUsing a common table expression to limit counts and report averages

O exemplo a seguir mostra o número médio de pedidos de vendas de todos os anos dos representantes de vendas.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. Usando várias definições CTE em uma única consultaUsing multiple CTE definitions in a single query

O exemplo a seguir mostra como definir mais de uma CTE em uma única consulta.The following example shows how to define more than one CTE in a single query. Observe que uma vírgula é usada para separar as definições de consulta CTE.Notice that a comma is used to separate the CTE query definitions. A função FORMAT, usada para exibir as quantidades monetárias em um formato de moeda, está disponível no SQL Server 2012 e posterior.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;    

Este é um conjunto de resultados parcial.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. Usando uma expressão de tabela comum recursiva para exibir vários níveis de recursãoUsing a recursive common table expression to display multiple levels of recursion

O exemplo a seguir mostra a lista hierárquica de gerentes e os funcionários subordinados a eles.The following example shows the hierarchical list of managers and the employees who report to them. O exemplo começa criando e populando a tabela 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. Usando uma expressão de tabela comum recursiva para exibir dois níveis de recursãoUsing a recursive common table expression to display two levels of recursion

O exemplo a seguir mostra os gerentes e os funcionários subordinados a eles.The following example shows managers and the employees reporting to them. O número de níveis retornado está limitado a dois.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. Usando uma expressão de tabela comum recursiva para exibir uma lista hierárquicaUsing a recursive common table expression to display a hierarchical list

O exemplo a seguir é construído com base no exemplo D com a adição dos nomes do gerente e dos funcionários, e seus cargos respectivos.The following example builds on Example D by adding the names of the manager and employees, and their respective titles. A hierarquia de gerentes e funcionários é evidenciada pelo recuo de cada nível.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. Usando MAXRECURSION para cancelar uma instruçãoUsing MAXRECURSION to cancel a statement

MAXRECURSION pode ser usado para impedir que uma CTE recursiva malformada entre em um loop infinito.MAXRECURSION can be used to prevent a poorly formed recursive CTE from entering into an infinite loop. O exemplo a seguir cria um loop infinito intencionalmente e usa a dica MAXRECURSION para limitar o número de níveis de recursão a dois.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);  

Depois que o erro de codificação for corrigido, MAXRECURSION não será mais necessário.After the coding error is corrected, MAXRECURSION is no longer required. O exemplo a seguir mostra o código corrigido.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. Usando uma expressão de tabela comum para percorrer seletivamente uma relação recursiva em uma instrução SELECTUsing a common table expression to selectively step through a recursive relationship in a SELECT statement

O exemplo a seguir mostra a hierarquia de assemblies e componentes do produto necessários para montar a bicicleta para 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. Usando uma CTE recursiva em uma instrução UPDATEUsing a recursive CTE in an UPDATE statement

O exemplo a seguir atualiza o valor de PerAssemblyQty de todas as peças usadas para construir o produto 'Road-550-W Yellow, 44' (ProductAssemblyID``800).The following example updates the PerAssemblyQty value for all parts that are used to build the product 'Road-550-W Yellow, 44' (ProductAssemblyID``800). A expressão de tabela comum retorna uma lista hierárquica das peças usadas para construir o ProductAssemblyID 800 e os componentes usados para criar essas peças, etc.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. Somente as linhas retornadas pela expressão de tabela comum são modificadas.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. Usando vários membros de ancoragem e recursivosUsing multiple anchor and recursive members

O exemplo a seguir usa vários membros de ancoragem e recursivos para retornar todos os ancestrais de uma pessoa especificada.The following example uses multiple anchor and recursive members to return all the ancestors of a specified person. Uma tabela é criada e valores inseridos para estabelecer a genealogia familiar retornada pela CTE recursiva.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. Usando funções analíticas em uma CTE recursivaUsing analytical functions in a recursive CTE

O exemplo a seguir mostra uma armadilha que pode ocorrer ao usar uma função analítica ou de agregação na parte recursiva de uma 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;  

Os resultados a seguir são os esperados da consulta.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  

Os resultados a seguir são os resultados reais da consulta.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 retorna 1 para cada passagem da parte recursiva da CTE porque apenas o subconjunto de dados daquele nível de recursão é transmitido para 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. Para cada uma das iterações da parte recursiva da consulta, apenas uma linha é transmitida para ROWNUMBER.For each of the iterations of the recursive part of the query, only one row is passed to ROWNUMBER.

Exemplos: SQL Data WarehouseSQL Data Warehouse e Parallel Data WarehouseParallel Data WarehouseExamples: SQL Data WarehouseSQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

L.L. Usando uma expressão de tabela comum dentro de uma instrução CTASUsing a common table expression within a CTAS statement

O exemplo a seguir cria uma nova tabela que contém o número total de ordens de venda por ano para cada representante de vendas no Ciclos da Adventure WorksAdventure Works Cycles.The following example creates a new table containing the total number of sales orders per year for each sales representative at Ciclos da Adventure WorksAdventure 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. Usando uma expressão de tabela comum dentro de uma instrução CETASUsing a common table expression within a CETAS statement

O exemplo a seguir cria uma nova tabela externa que contém o número total de ordens de venda por ano para cada representante de vendas no Ciclos da Adventure WorksAdventure Works Cycles.The following example creates a new external table containing the total number of sales orders per year for each sales representative at Ciclos da Adventure WorksAdventure 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. Usando várias CTEs separadas por vírgula em uma instruçãoUsing multiple comma separated CTEs in a statement

O exemplo a seguir demonstra como incluir duas CTEs em uma única instrução.The following example demonstrates including two CTEs in a single statement. As CTEs não podem ser aninhadas (sem recursão).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;  

Consulte TambémSee Also

CREATE VIEW (Transact-SQL) CREATE VIEW (Transact-SQL)
DELETE (Transact-SQL) DELETE (Transact-SQL)
EXCEPT e 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)