WITH common_table_expression (Transact-SQL)

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)Ponto de extremidade de SQL no Microsoft FabricWarehouse no Microsoft Fabric

Especifica um conjunto de resultados nomeado temporário, conhecido como uma CTE (expressão de tabela comum). Ela é derivada de uma consulta simples e definida no escopo de execução de uma única instrução SELECT, INSERT, UPDATE, DELETE ou MERGE. Esta cláusula também pode ser usada em uma instrução CREATE VIEW como parte da instrução SELECT que a define. Uma expressão de tabela comum pode incluir referências a si mesma. É o que chamamos de expressão de tabela comum recursiva.

Convenções de sintaxe de Transact-SQL

Sintaxe

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

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

Observação

Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.

Argumentos

expression_name

Um identificador válido para a expressão de tabela comum. expression_name deverá ser diferente do nome de qualquer outra expressão de tabela comum definida na mesma cláusula WITH <common_table_expression>, mas expression_name poderá ser igual ao nome de uma exibição ou tabela base. Qualquer referência a expression_name na consulta usa a expressão de tabela comum, e não o objeto base.

column_name

Especifica um nome de coluna na expressão de tabela comum. Não são permitidos nomes duplicados em uma única definição de CTE. O número de nomes de coluna especificado deve corresponder ao número de colunas no conjunto de resultados da 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.

CTE_query_definition

Especifica uma instrução SELECT cujo conjunto de resultados popula a expressão de tabela comum. 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. Para obter mais informações, confira a seção Comentários e 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.

Diretrizes para criar e usar expressões de tabela comuns

As diretrizes a seguir se aplicam a expressões de tabela comuns não recursivas. 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.

  • Uma CTE deve ser seguida por uma única instrução SELECT, INSERT, UPDATE ou DELETE que referencia algumas ou todas as colunas da CTE. 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.

  • É possível ter várias definições de consulta CTE em uma CTE não recursiva. As definições devem ser combinadas por um destes operadores de conjunto: UNION ALL, UNION, INTERSECT ou EXCEPT.

  • Uma CTE pode fazer referência a si mesma e a CTEs definidas anteriormente na mesma cláusula WITH. Não é permitido fazer referência antecipada.

  • Não é permitida a especificação de mais de uma cláusula WITH em uma CTE. 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.

  • As seguintes cláusulas não podem ser usadas na CTE_query_definition:

    • ORDER BY (exceto quando uma cláusula TOP for especificada)

    • INTO

    • Cláusula OPTION com dicas de consulta

    • 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.

  • Uma consulta que faça referência a uma CTE pode ser usada para definir um cursor.

  • As tabelas em servidores remotos podem ser referenciadas na 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. Quando isso ocorre, a consulta retorna um erro.

Diretrizes para definir e usar expressões de tabela recursivas comuns

As seguintes diretrizes aplicam-se à definição de uma expressão de tabela comum recursiva:

  • 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. É 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. Todas as definições de consulta de CTE são membros de ancoragem, a menos que façam referência à própria CTE.

  • Os membros de ancoragem devem ser combinados por um destes operadores de conjunto: UNION ALL, UNION, INTERSECT ou 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.

  • O número de colunas nos membros de ancoragem e recursivos deve ser o mesmo.

  • 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.

  • A cláusula FROM de um membro recursivo deve referenciar apenas uma vez o expression_name da CTE.

  • Os seguintes itens não são permitidos na CTE_query_definition de um membro recursivo:

As seguintes diretrizes aplicam-se ao uso de uma expressão de tabela comum recursiva:

  • Todas as colunas retornadas pela CTE recursiva aceitam valores nulos, independentemente da possibilidade de nulidade das colunas retornadas pelas instruções SELECT participantes.

  • Uma CTE recursiva incorretamente composta pode causar um loop infinito. 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. 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. Isso permite controlar a execução da instrução até que você resolva o problema de código que está criando o loop. O padrão para todo o servidor é 100. Quando 0 é especificado, nenhum limite é aplicado. Apenas um valor MAXRECURSION pode ser especificado por instrução. Para obter mais informações, veja Dicas de consulta (Transact-SQL).

  • Uma exibição que contém uma expressão de tabela comum recursiva não pode ser usada para atualizar dados.

  • É possível definir cursores em consultas usando CTEs. A CTE é o argumento select_statement que define o conjunto de resultados do cursor. Apenas cursores de somente avanço rápido e estáticos (instantâneos) são permitidos para CTEs recursivas. Se outro tipo de cursor for especificado em uma CTE recursiva, o tipo de cursor será convertido em estático.

  • As tabelas em servidores remotos podem ser referenciadas na 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. Se essa for uma consulta CTE, Index Spool/Lazy Spools serão exibidos no plano de consulta e terão o predicado adicional WITH STACK. Essa é uma maneira de confirmar a recursão correta.

  • 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. 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. Para obter mais informações, veja o exemplo I. Usar funções analíticas em uma CTE recursiva seguinte.

Funcionalidades e limitações de expressões de tabela comuns no Azure Synapse Analytics e Analytics Platform System (PDW)

A implementação atual das CTEs no Azure Synapse Analytics e PDW (Analytics Platform System) apresenta os seguintes recursos e limitações:

  • Uma CTE pode ser especificada em uma instrução SELECT.

  • Uma CTE pode ser especificada em uma instrução CREATE VIEW.

  • Uma CTE pode ser especificada em uma instrução CREATE TABLE AS SELECT (CTAS).

  • Uma CTE pode ser especificada em uma instrução CREATE REMOTE TABLE AS SELECT (CRTAS).

  • Uma CTE pode ser especificada em uma instrução CREATE EXTERNAL TABLE AS SELECT (CETAS).

  • Uma tabela remota pode ser referenciada em uma CTE.

  • Uma tabela externa pode ser referenciada em uma CTE.

  • Várias definições de consulta CTE podem ser definidas em uma CTE.

  • Uma CTE deve ser seguida por uma única instrução SELECT. Não há suporte para as instruçõesINSERT, UPDATE, DELETEe MERGE.

  • 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).

  • Não é permitida a especificação de mais de uma cláusula WITH em uma CTE. 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.

  • Uma cláusula ORDER BY não pode ser usada na CTE_query_definition, exceto quando uma cláusula TOP é especificada.

  • 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.

  • Quando usadas em instruções preparadas por sp_prepare, CTEs se comportarão da mesma maneira que outras instruções SELECT no 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 Server e outras instruções do PDW, devido à maneira como a associação é implementada para sp_prepare. Se o 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.

Exemplos

a. Criar uma expressão de tabela comum

O exemplo a seguir mostra o número total de pedidos de vendas por ano para cada representante de vendas na Ciclos da Adventure Works.

-- 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. Usar uma expressão de tabela comum para limitar contagens e médias de relatório

O exemplo a seguir mostra o número médio de pedidos de vendas de todos os anos dos representantes de vendas.

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. Usar várias definições CTE em uma única consulta

O exemplo a seguir mostra como definir mais de uma CTE em uma única consulta. Observe que uma vírgula é usada para separar as definições de consulta CTE. 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.

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.

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. Usar uma expressão de tabela comum recursiva para exibir vários níveis de recursão

O exemplo a seguir mostra a lista hierárquica de gerentes e os funcionários subordinados a eles. O exemplo começa criando e populando a tabela dbo.MyEmployees.

-- 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 SMALLINT NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC),
CONSTRAINT FK_MyEmployees_ManagerID_EmployeeID FOREIGN KEY (ManagerID) REFERENCES dbo.MyEmployees (EmployeeID)
);
-- 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);

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;

Usar uma expressão de tabela comum recursiva para exibir dois níveis de recursão

O exemplo a seguir mostra os gerentes e os funcionários subordinados a eles. O número de níveis retornado está limitado a dois.

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 ;

Usar uma expressão de tabela comum recursiva para exibir uma lista hierárquica

O exemplo a seguir adiciona os nomes dos gerente e dos funcionários e seus respectivos cargos. A hierarquia de gerentes e funcionários é evidenciada pelo recuo de cada nível.

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;

Usar MAXRECURSION para cancelar uma instrução

MAXRECURSION pode ser usado para impedir que uma CTE recursiva malformada entre em um loop infinito. 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.

--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. O exemplo a seguir mostra o código corrigido.

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;

E. Usar uma expressão de tabela comum para seletivamente executar em etapas uma relação recursiva em uma instrução SELECT

O exemplo a seguir mostra a hierarquia de assemblies e componentes do produto necessários para montar a bicicleta para ProductAssemblyID = 800.

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

F. Usar uma CTE recursiva em uma instrução UPDATE

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). 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. Somente as linhas retornadas pela expressão de tabela comum são modificadas.

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

H. Usar vários membros de ancoragem e recursivos

O exemplo a seguir usa vários membros de ancoragem e recursivos para retornar todos os ancestrais de uma pessoa especificada. Uma tabela é criada e valores inseridos para estabelecer a genealogia familiar retornada pela CTE recursiva.

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

I. Usar funções analíticas em uma CTE recursiva

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.

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.

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.

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. Para cada uma das iterações da parte recursiva da consulta, apenas uma linha é transmitida para ROWNUMBER.

Exemplos: Azure Synapse Analytics e PDW (Analytics Platform System)

J. Usar uma expressão de tabela comum dentro de uma instrução CTAS

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 Works.

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

K. Usar uma expressão de tabela comum dentro de uma instrução CETAS

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 Works.

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

L. Usar várias CTEs separadas por vírgula em uma instrução

O exemplo a seguir demonstra como incluir duas CTEs em uma única instrução. As CTEs não podem ser aninhadas (sem recursão).

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;

Confira também