Compartilhar via

Consultas recursivas usando expressões de tabela comuns (Transact-SQL)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSistema de Plataforma de Análise (PDW)Banco de dados SQL no Microsoft Fabric

Uma CTE (expressão de tabela comum) fornece a vantagem significativa de poder fazer referência a si mesma, criando assim uma CTE recursiva. Um CTE recursivo é aquele em que um CTE inicial é executado repetidamente para retornar subconjuntos de dados até que o conjunto de resultados completo seja obtido.

Uma consulta é conhecida como uma consulta recursiva quando faz referência a uma CTE recursiva. Retornar dados hierárquicos é um uso comum de consultas recursivas. Por exemplo, exibir funcionários em um gráfico organizacional ou dados em um cenário de cobrança de materiais no qual um produto pai tem um ou mais componentes e esses componentes podem ter subcomponentes, ou podem ser componentes, de outros pais.

Uma CTE recursiva pode simplificar muito o código necessário para executar uma consulta recursiva dentro de uma SELECTinstrução, , INSERTUPDATEDELETECREATE VIEW ou instrução. Em versões anteriores do SQL Server, uma consulta recursiva geralmente requer o uso de tabelas temporárias, cursores e lógica para controlar o fluxo das etapas recursivas. Para obter mais informações sobre expressões de tabela comuns, consulte WITH common_table_expression.

No Microsoft Fabric, no Fabric Data Warehouse e no ponto de extremidade de análise do SQL, ambos dão suporte a CTEs padrão, sequenciais e aninhados, mas não a CTEs recursivas.

Estrutura de um CTE recursivo

A estrutura da CTE recursiva em Transact-SQL é semelhante a rotinas recursivas em outras linguagens de programação. Embora uma rotina recursiva em outras linguagens retorne um valor escalar, uma CTE recursiva pode retornar várias linhas.

Um CTE recursivo consiste em três elementos:

  1. Invocação da rotina.

    A primeira invocação do CTE recursivo consiste em uma ou mais definições de consulta CTE unidas por UNION ALLoperadores, UNIONou EXCEPTINTERSECT operadores. Como essas definições de consulta formam o conjunto de resultados base da estrutura CTE, elas são conhecidas como membros de âncora.

    As definições de consulta CTE são consideradas membros de âncora, a menos que referenciem a própria CTE. Todas as definições de consulta de membro de âncora devem ser posicionadas antes da primeira definição de membro recursivo e um UNION ALL operador deve ser usado para ingressar o último membro de âncora com o primeiro membro recursivo.

  2. Invocação recursiva da rotina.

    A invocação recursiva inclui uma ou mais definições de consulta CTE unidas por UNION ALL operadores que fazem referência à própria CTE. Essas definições de consulta são conhecidas como membros recursivos.

  3. Verificação de encerramento.

    A verificação de término está implícita; a recursão é interrompida quando nenhuma linha é retornada da invocação anterior.

Note

Um CTE recursivo composto incorretamente 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. Ao testar os resultados de uma consulta recursiva, você pode limitar o número de níveis de recursão permitidos para uma instrução específica usando a MAXRECURSION dica e um valor entre 0 e 32.767 na OPTION cláusula da INSERTinstrução , UPDATEou DELETESELECT instrução.

Para obter mais informações, consulte:

Pseudocódigo e semântica

A estrutura de CTE recursiva deve conter pelo menos um membro de âncora e um membro recursivo. O pseudocódigo a seguir mostra os componentes de um CTE recursivo simples que contém um único membro de âncora e um único membro recursivo.

WITH cte_name ( column_name [ ,...n ] )
AS
(
    CTE_query_definition -- Anchor member is defined.
    UNION ALL
    CTE_query_definition -- Recursive member is defined referencing cte_name.
)

-- Statement using the CTE
SELECT *
FROM cte_name

A semântica da execução recursiva é a seguinte:

  1. Divida a expressão CTE em membros de ancoragem e recursivos.
  2. Execute os membros de âncora criando a primeira invocação ou conjunto de resultados base (T0).
  3. Execute os membros recursivos com Ti uma entrada e Ti + 1 como saída.
  4. Repita a etapa 3 até que um conjunto vazio seja retornado.
  5. Retorne o conjunto de resultados. Este é um UNION ALL de T0 para Tn.

Examples

O exemplo a seguir mostra a semântica da estrutura de CTE recursiva retornando uma lista hierárquica de funcionários, começando pelo funcionário de alto escalão, no AdventureWorks2025 banco de dados. Um passo a passo da execução de código segue o exemplo.

Crie uma tabela de funcionários:

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

Preencha a tabela com valores:

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

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS (
-- Anchor member definition
SELECT e.ManagerID,
           e.EmployeeID,
           e.Title,
           edh.DepartmentID,
           0 AS Level
    FROM dbo.MyEmployees AS e
         INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
             ON e.EmployeeID = edh.BusinessEntityID
            AND edh.EndDate IS NULL
    WHERE ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.ManagerID,
           e.EmployeeID,
           e.Title,
           edh.DepartmentID,
           Level + 1
    FROM dbo.MyEmployees AS e
         INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
             ON e.EmployeeID = edh.BusinessEntityID
            AND edh.EndDate IS NULL
         INNER JOIN DirectReports AS d
             ON e.ManagerID = d.EmployeeID)
-- Statement that executes the CTE
SELECT ManagerID,
       EmployeeID,
       Title,
       DeptID,
       Level
FROM DirectReports
     INNER JOIN HumanResources.Department AS dp
         ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing'
      OR Level = 0;
GO

Passo a passo de código de exemplo

O CTE DirectReportsrecursivo define um membro de âncora e um membro recursivo.

O membro de âncora retorna o conjunto T0de resultados base. Este é o funcionário mais alto da empresa. Ou seja, um funcionário que não se reporta a um gerente.

Aqui está o conjunto de resultados retornado pelo membro de âncora:

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
NULL      1          Chief Executive Officer        0

O membro recursivo retorna os subordinados diretos do funcionário no conjunto de resultados do membro de âncora. Isso é obtido por uma operação de junção entre a tabela Funcionário e a DirectReports CTE. É essa referência à própria CTE que estabelece a invocação recursiva. Com base no funcionário no CTE DirectReports como entrada (Ti), a junção (MyEmployees.ManagerID = DirectReports.EmployeeID) retorna como saída (Ti + 1), os funcionários que têm (Ti) como gerente.

Portanto, a primeira iteração do membro recursivo retorna este conjunto de resultados:

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
1         273        Vice President of Sales       1

O membro recursivo é ativado repetidamente. A segunda iteração do membro recursivo usa o conjunto de resultados de linha única na etapa 3 (contendo um EmployeeID de ) como o valor de 273entrada e retorna este conjunto de resultados:

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
273       16         Marketing Manager             2
273       274        North American Sales Manager  2
273       285        Pacific Sales Manager         2

A terceira iteração do membro recursivo usa o conjunto de resultados anterior como o valor de entrada e retorna este conjunto de resultados:

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
16        23         Marketing Specialist          3
274       275        Sales Representative          3
274       276        Sales Representative          3
285       286        Sales Representative          3

O conjunto de resultados final retornado pela consulta em execução é a união de todos os conjuntos de resultados gerados pela âncora e membros recursivos.

Veja aqui o conjunto de resultados.

ManagerID EmployeeID Title                         Level
--------- ---------- ----------------------------- ------
NULL      1          Chief Executive Officer       0
1         273        Vice President of Sales       1
273       16         Marketing Manager             2
273       274        North American Sales Manager  2
273       285        Pacific Sales Manager         2
16        23         Marketing Specialist          3
274       275        Sales Representative          3
274       276        Sales Representative          3
285       286        Sales Representative          3

Conteúdo relacionado

  • Last updated on