Partilhar via

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

Aplica-se a:Banco de Dados SQL doAzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)Banco de Dados SQL no Microsoft Fabric

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

Uma consulta é referida como uma consulta recursiva quando faz referência a uma CTE recursiva. O retorno de dados hierárquicos é um uso comum de consultas recursivas. Por exemplo, exibir funcionários em um organograma ou dados em um cenário de lista de materiais em que um produto pai tem um ou mais componentes e esses componentes podem ter subcomponentes, ou podem ser componentes, de outros pais.

Um CTE recursivo pode simplificar muito o código necessário para executar uma consulta recursiva dentro de uma SELECTinstrução , INSERT, UPDATE, DELETE, ou CREATE VIEW . 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, o Fabric Data Warehouse e o ponto de extremidade de análise SQL oferecem suporte a CTEs padrão, sequenciais e aninhados, mas não a CTEs recursivos.

Estrutura de um CTE recursivo

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

Uma CTE recursiva 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 ALL, UNION, EXCEPTou INTERSECT operadores. Como essas definições de consulta formam o conjunto de resultados base da estrutura CTE, elas são chamadas de membros âncora.

    As definições de consulta CTE são consideradas membros âncora, a menos que façam referência à própria CTE. Todas as definições de consulta de membro âncora devem ser posicionadas antes da primeira definição de membro recursivo, e um UNION ALL operador deve ser usado para unir o último membro â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 ao próprio CTE. Essas definições de consulta são chamadas de membros recursivos.

  3. Verificação de rescisão.

    A verificação de rescisão está implícita; A recursão para quando nenhuma linha é retornada da chamada anterior.

Note

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. 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 dica MAXRECURSION e um valor entre 0 e 32.767 na OPTION cláusula da INSERTinstrução , UPDATE, DELETE, ou SELECT .

Para obter mais informações, consulte:

Pseudocódigo e semântica

A estrutura CTE recursiva deve conter pelo menos um membro âncora e um membro recursivo. O pseudocódigo a seguir mostra os componentes de um CTE recursivo simples que contém um único membro â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 âncora e recursivos.
  2. Execute os membros âncora criando a primeira invocação ou o conjunto de resultados base (T0).
  3. Execute os membros recursivos com Ti como entrada e Ti + 1 como saída.
  4. Repita a etapa 3 até que um conjunto vazio seja retornado.
  5. Retornar o conjunto de resultados. Este é um UNION ALL dos T0Tn.

Examples

O exemplo a seguir mostra a semântica da estrutura CTE recursiva retornando uma lista hierárquica de funcionários, começando com o AdventureWorks2025 funcionário de classificação mais alta, no banco de dados. Um passo a passo da execução do 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

Exemplo de passo a passo do código

O CTE recursivo, DirectReports, define um membro âncora e um membro recursivo.

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

Aqui está o conjunto de resultados retornado pelo membro â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 âncora. Isto é conseguido através de uma operação de junção entre a tabela Employee e o DirectReports CTE. É essa referência ao próprio 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 seu gerente.

Portanto, a primeira iteração do membro recursivo retorna esse 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 entrada e retorna esse conjunto de 273resultados:

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 esse 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 pelos membros âncora e recursivos.

Aqui está 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