Partilhar via

COM common_table_expression (Transact-SQL)

Aplica-se a:SQL ServerBase de Dados SQL do AzureInstância Gerida do Azure SQLAzure Synapse AnalyticsSistema de Plataforma de Análise (PDW)Ponto de Extremidade de Análise SQL no Microsoft FabricArmazém no Microsoft FabricBase de Dados SQL no Microsoft Fabric

Especifica um conjunto de resultados nomeado temporário, conhecido como uma expressão de tabela comum (CTE). Isso é derivado de uma consulta simples e definido dentro do escopo de execução de uma única SELECTinstrução , INSERT, UPDATE, MERGE, ou DELETE . Esta cláusula também pode ser usada em uma CREATE VIEW declaração como parte de sua declaração definidora SELECT . Uma expressão de tabela comum pode incluir referências a si mesma. Isso é conhecido como uma expressão de tabela comum recursiva.

Para obter mais informações, consulte Consultas recursivas usando expressões de tabela comuns.

Transact-SQL convenções de sintaxe

Syntax

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

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

Arguments

expression_name

Um identificador válido para a expressão de tabela comum. expression_name deve ser diferente do nome de qualquer outra expressão de tabela comum definida na mesma WITH <common_table_expression> cláusula, mas expression_name pode ser o mesmo que o nome de uma tabela base ou exibição. 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. Nomes duplicados dentro de uma única definição de CTE não são permitidos. O número de nomes de colunas especificado deve corresponder ao número de colunas no conjunto de resultados do CTE_query_definition. A lista de nomes de colunas é opcional somente se nomes distintos para todas as colunas resultantes forem fornecidos na definição de consulta.

CTE_query_definition

Especifica uma SELECT instrução cujo conjunto de resultados preenche a expressão de tabela comum. A SELECT instrução para CTE_query_definition deve atender aos mesmos requisitos que para criar uma exibição, exceto que um CTE não pode definir outro CTE. Para obter mais informações, consulte a seção Comentários e CREATE VIEW.

Se mais de um CTE_query_definition for definido, as definições de consulta deverão ser unidas por um destes operadores de conjunto: UNION ALL, UNION, EXCEPT, ou INTERSECT.

Diretrizes de uso

Os resultados da consulta de expressões de tabela comuns não são materializados. Cada referência externa ao conjunto de resultados nomeado requer que a consulta definida seja reexecutada. Para consultas que exigem várias referências ao conjunto de resultados nomeado, considere usar um objeto temporário .

Não é possível executar um procedimento armazenado em uma expressão de tabela comum.

Para obter diretrizes de uso sobre CTEs recursivas e não recursivas, consulte as seções a seguir.

Diretrizes para expressões de tabela comuns não recursivas

Note

As diretrizes a seguir se aplicam a expressões de tabela comuns não recursivas. Para obter diretrizes que se aplicam a expressões de tabela comuns recursivas, consulte Diretrizes para expressões de tabela comuns recursivas.

Uma CTE deve ser seguida por uma única SELECT, INSERT, UPDATE, MERGE, ou DELETE instrução que faça referência a algumas ou a todas as colunas CTE. Uma CTE também pode ser especificada em uma CREATE VIEW instrução como parte da instrução definidora SELECT da exibição.

Várias definições de consulta CTE podem ser definidas 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 previamente definidas na mesma WITH cláusula. A referência direta não é permitida.

Não é permitido especificar mais de uma WITH cláusula em um CTE. Por exemplo, se um CTE_query_definition contiver uma subconsulta, essa subconsulta não poderá conter uma cláusula aninhada WITH que defina outra CTE.

Para obter mais informações sobre CTEs aninhadas no Microsoft Fabric, consulte Nested Common Table Expression (CTE) in Fabric data warehousing (Transact-SQL).

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

  • ORDER BY (exceto quando é especificada uma TOP ou OFFSET/FETCH cláusula)
  • INTO
  • OPTION cláusula com dicas de consulta 1
  • FOR BROWSE

1 A OPTION cláusula não pode ser usada dentro de uma definição de CTE. Ele só pode ser usado na declaração mais SELECT externa.

Quando um CTE é usado em uma instrução que faz parte de um lote, a instrução antes dele deve ser seguida por um ponto-e-vírgula.

Uma consulta que faz referência a um CTE pode ser usada para definir um cursor.

As tabelas em servidores remotos podem ser referenciadas no CTE.

Ao executar uma CTE, quaisquer dicas que façam referência a uma CTE podem entrar em conflito com outras dicas que são descobertas quando a CTE acessa suas tabelas subjacentes, da mesma maneira que as dicas que fazem referência a exibições em consultas. Quando isso ocorre, a consulta retorna um erro.

Diretrizes para expressões de tabela comuns recursivas

Note

As diretrizes a seguir se aplicam à definição de uma expressão de tabela comum recursiva. Para obter diretrizes que se aplicam a CTEs não recursivas, consulte Diretrizes para expressões de tabela comuns não recursivas.

A definição de CTE recursiva deve conter pelo menos duas definições de consulta CTE, um membro âncora e um membro recursivo. Vários membros âncora e membros recursivos podem ser definidos; no entanto, todas as definições de consulta de membro âncora devem ser colocadas antes da primeira definição de membro recursivo. Todas as definições de consulta CTE são membros âncora, a menos que façam referência à própria CTE.

Os membros âncora 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 âncora e o primeiro membro recursivo, e ao combinar vários membros recursivos.

O número de colunas na âncora e membros 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 âncora.

A FROM cláusula de um membro recursivo deve referir-se apenas uma vez ao CTE expression_name.

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

  • SELECT DISTINCT
  • GROUP BY
  • PIVOT 1
  • HAVING
  • Agregação escalar
  • TOP
  • LEFT, RIGHT, OUTER JOIN (INNER JOIN é permitido)
  • Subqueries
  • Uma dica aplicada a uma referência recursiva a uma CTE dentro de um CTE_query_definition.

1 Quando o nível de compatibilidade do banco de dados é 110 ou superior. Consulte Alterações significativas nos recursos do Mecanismo de Banco de Dados no SQL Server 2016.

As diretrizes a seguir se aplicam ao uso de uma expressão de tabela comum recursiva:

  • Todas as colunas retornadas pelo CTE recursivo são anuláveis, independentemente da anulabilidade das colunas retornadas pelas instruções participantes SELECT .

  • Uma CTE recursiva composta incorretamente pode causar um loop infinito. Por exemplo, se a definição de consulta de membro recursivo retornar os mesmos valores para as colunas pai e filho, um loop infinito será criado. Para evitar um loop infinito, você pode 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 32767 na OPTION cláusula da INSERTinstrução , UPDATE, DELETE, ou SELECT . Isso permite controlar a execução da instrução até resolver o problema de código que está criando o loop. O padrão em todo o servidor é 100. Quando 0 é especificado, nenhum limite é aplicado. Apenas um MAXRECURSION valor pode ser especificado por instrução. Para obter mais informações, consulte Dicas de consulta.

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

  • Os cursores podem ser definidos em consultas usando CTEs. O CTE é o argumento select_statement que define o conjunto de resultados do cursor. Somente cursores estáticos e de avanço rápido (instantâneo) são permitidos para CTEs recursivos. 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 no CTE. Se o servidor remoto for referenciado no membro recursivo do CTE, um spool será criado para cada tabela remota para que as tabelas possam ser acessadas repetidamente localmente. Se for uma consulta CTE, os Spools de Índice/Lazy Spools serão exibidos no plano de consulta e terão o predicado adicional WITH STACK . Esta é uma maneira de confirmar a recursão adequada.

  • As funções analíticas e agregadas na parte recursiva do CTE são aplicadas ao conjunto para o nível de recursão atual e não ao conjunto para o CTE. Funções como ROW_NUMBER operar apenas no subconjunto de dados passados a eles pelo nível de recursão atual e não todo o conjunto de dados passados para a parte recursiva do CTE. Para obter mais informações, consulte o exemplo I. Usar funções analíticas em uma CTE recursiva a seguir.

Expressões de tabela comuns no Azure Synapse Analytics and Analytics Platform System (PDW)

A implementação atual de CTEs no Azure Synapse Analytics and Analytics Platform System (PDW) tem os seguintes recursos e requisitos:

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

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

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

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

  • Uma CTE pode ser especificada numa CREATE EXTERNAL TABLE AS SELECT declaração (CETAS).

  • Uma tabela remota pode ser referenciada a partir de um CTE.

  • Uma tabela externa pode ser referenciada a partir de um CTE.

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

  • Uma CTE pode ser seguida por SELECT, INSERT, UPDATE, DELETEou MERGE declarações.

  • Não há suporte para uma expressão de tabela comum que inclua referências a si mesma (uma expressão de tabela comum recursiva).

  • Não é permitido especificar mais de uma WITH cláusula em um CTE. Por exemplo, se uma definição de consulta CTE contiver uma subconsulta, essa subconsulta não poderá conter uma cláusula aninhada WITH que defina outra CTE.

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

  • Quando um CTE é usado em uma instrução que faz parte de um lote, a instrução antes dele deve ser seguida por um ponto-e-vírgula.

  • Quando usadas em declarações preparadas pela sp_prepare, as CTEs se comportam da mesma forma que outras SELECT declarações na APS PDW. No entanto, se CTEs forem usados como parte do CETAS preparado pelo sp_prepare, o comportamento poderá ser adiado do SQL Server e de outras instruções APS PDW devido à maneira como a vinculação é implementada para sp_prepare. Se SELECT essa referência CTE está usando uma coluna errada que não existe no CTE, o sp_prepare passa sem detetar o erro, mas o erro é lançado durante sp_execute em vez disso.

Examples

A. Criar uma expressão de tabela comum

O exemplo a seguir mostra o número total de ordens de venda por ano para cada representante de vendas no Adventure 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. Usar uma expressão de tabela comum para limitar contagens e relatar médias

O exemplo a seguir mostra o número médio de ordens de venda para todos os anos para os 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 de CTE em uma única consulta

O exemplo a seguir mostra como definir mais de um CTE em uma única consulta. Uma vírgula é usada para separar as definições de consulta CTE. A FORMAT função, usada para exibir os valores monetários em um formato de moeda, foi introduzida no SQL Server 2012 (11.x).

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
     INNER JOIN Sales_Quota_CTE
         ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID
        AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear
ORDER BY SalesPersonID, SalesYear;

Aqui está um conjunto de resultados parciais.

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 que se reportam a eles. O exemplo começa criando e preenchendo a dbo.MyEmployees tabela.

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

Use 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 que se reportam a eles. O número de níveis retornados é 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 do gerente e dos funcionários e seus respetivos títulos. A hierarquia de gerentes e funcionários é adicionalmente enfatizada 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
         INNER JOIN DirectReports AS d
             ON e.ManagerID = d.EmployeeID
)
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports
ORDER BY Sort;

Use MAXRECURSION para cancelar uma instrução

MAXRECURSION pode ser usado para evitar que uma CTE recursiva mal formada entre em um loop infinito. O exemplo a seguir cria intencionalmente um loop infinito 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
         INNER 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 é corrigido, MAXRECURSION não é 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
         INNER JOIN cte
             ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;

E. Use uma expressão de tabela comum para percorrer seletivamente uma relação recursiva em uma instrução SELECT

O exemplo a seguir mostra a hierarquia de montagens de produtos e componentes necessários para construir 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 um CTE recursivo em uma instrução UPDATE

O exemplo a seguir atualiza o PerAssemblyQty valor de todas as partes usadas para criar o produto 'Road-550-W Yellow, 44' (ProductAssemblyID 800). A expressão de tabela comum retorna uma lista hierárquica de partes que são usadas para criar ProductAssemblyID 800 e os componentes que são usados para criar essas partes, e assim por diante. 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
     INNER JOIN Parts AS d
         ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0;

H. Usar vários membros âncora e recursivos

O exemplo a seguir usa vários membros âncora e recursivos para retornar todos os antepassados de uma pessoa especificada. É criada uma tabela e inseridos valores para estabelecer a genealogia familiar devolvida pelo CTE recursivo.

-- 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 agregada 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
         INNER JOIN vw AS t
             ON t.itmID = r.itmIDComp
)
SELECT Lvl, N FROM r;

Os resultados a seguir são os resultados esperados para a 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 do CTE porque apenas o subconjunto de dados para esse nível de recursão é passado para ROWNUMBER. Para cada uma das iterações da parte recursiva da consulta, apenas uma linha é passada para ROWNUMBER.

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

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

O exemplo a seguir cria uma nova tabela contendo o número total de ordens de venda por ano para cada representante de vendas na Adventure Works Cycles.

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. Utilizar uma expressão de tabela comum numa declaração CETAS

O exemplo a seguir cria uma nova tabela externa contendo o número total de ordens de venda por ano para cada representante de vendas no Adventure Works Cycles.

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ários CTEs separados por vírgulas em uma instrução

O exemplo a seguir demonstra a inclusão de dois CTEs em uma única instrução. Os CTEs não podem ser aninhados (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;

Conteúdo relacionado

  • Last updated on