Condividi tramite

Query ricorsive che usano espressioni di tabella comuni (Transact-SQL)

Si applica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSistema di Piattaforma Analitica (PDW)Database SQL in Microsoft Fabric

Un'espressione di tabella comune (CTE) offre il vantaggio significativo di poter fare riferimento a se stessa, creando così un CTE ricorsivo. Un CTE ricorsivo è uno in cui un CTE iniziale viene eseguito ripetutamente per restituire subset di dati fino a quando non viene ottenuto il set di risultati completo.

Una query viene definita query ricorsiva quando fa riferimento a un CTE ricorsivo. La restituzione di dati gerarchici è un uso comune delle query ricorsive. Ad esempio, la visualizzazione dei dipendenti in un organigramma o i dati in uno scenario di fatturazione di materiali in cui un prodotto padre ha uno o più componenti e tali componenti potrebbero avere sottocomponenti o potrebbero essere componenti di altri genitori.

Un CTE ricorsivo può semplificare notevolmente il codice necessario per eseguire una query ricorsiva all'interno di un'istruzione SELECT, INSERT, UPDATEDELETE, o CREATE VIEW . Nelle versioni precedenti di SQL Server, una query ricorsiva richiede in genere l'uso di tabelle temporanee, cursori e logica per controllare il flusso dei passaggi ricorsivi. Per altre informazioni sulle espressioni di tabella comuni, vedere WITH common_table_expression.

In Microsoft Fabric, Fabric Data Warehouse e l'endpoint di analisi SQL supportano sia le CTE standard, sequenziali che annidate, ma non le CTE ricorsive.

Struttura di un CTE ricorsivo

La struttura del CTE ricorsivo in Transact-SQL è simile alle routine ricorsive in altri linguaggi di programmazione. Anche se una routine ricorsiva in altri linguaggi restituisce un valore scalare, un CTE ricorsivo può restituire più righe.

Un CTE ricorsivo è costituito da tre elementi:

  1. Chiamata della routine.

    La prima chiamata dell'CTE ricorsiva è costituita da una o più definizioni di query CTE unite da UNION ALLoperatori , UNIONEXCEPT, o INTERSECT . Poiché queste definizioni di query formano il set di risultati di base della struttura CTE, vengono definite membri di ancoraggio.

    Le definizioni di query CTE sono considerate membri di ancoraggio, a meno che non facciano riferimento all'CTE stesso. Tutte le definizioni di query membro di ancoraggio devono essere posizionate prima della prima definizione di membro ricorsivo e deve essere usato un UNION ALL operatore per unire l'ultimo membro di ancoraggio con il primo membro ricorsivo.

  2. Chiamata ricorsiva della routine.

    La chiamata ricorsiva include una o più definizioni di query CTE unite da UNION ALL operatori che fanno riferimento al CTE stesso. Queste definizioni di query vengono definite membri ricorsivi.

  3. Controllo della terminazione.

    Il controllo di terminazione è implicito; la ricorsione si interrompe quando non viene restituita alcuna riga dalla chiamata precedente.

Note

Un CTE ricorsivo composto in modo non corretto può causare un ciclo infinito. Ad esempio, se la definizione della query del membro ricorsivo restituisce gli stessi valori per entrambe le colonne padre e figlio, si crea un ciclo infinito. Quando si testano i risultati di una query ricorsiva, è possibile limitare il numero di livelli di ricorsione consentiti per un'istruzione specifica usando l'hint MAXRECURSION e un valore compreso tra 0 e 32.767 nella clausola dell'istruzione OPTIONINSERT , UPDATEDELETE, o SELECT .

Per altre informazioni, vedere:

Pseudocodice e semantica

La struttura CTE ricorsiva deve contenere almeno un membro di ancoraggio e un membro ricorsivo. Lo pseudocodice seguente mostra i componenti di un CTE ricorsivo semplice che contiene un singolo membro di ancoraggio e un singolo membro ricorsivo.

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

La semantica dell'esecuzione ricorsiva è la seguente:

  1. Suddividere l'espressione CTE in membri ancoraggi e ricorsivi.
  2. Eseguire i membri di ancoraggio creando la prima chiamata o il set di risultati di base (T0).
  3. Eseguire i membri ricorsivi con Ti come input e Ti + 1 come output.
  4. Ripetere il passaggio 3 fino a quando non viene restituito un set vuoto.
  5. Restituisce il set di risultati. Si tratta di un oggetto UNION ALL di T0 a Tn.

Examples

Nell'esempio seguente viene illustrata la semantica della struttura CTE ricorsiva restituendo un elenco gerarchico di dipendenti, a partire dal dipendente con la classificazione più alta, nel AdventureWorks2025 database. Una procedura dettagliata dell'esecuzione del codice segue l'esempio.

Creare una tabella employee:

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

Popolare la tabella con valori:

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

Procedura dettagliata del codice di esempio

L'oggetto CTE ricorsivo, DirectReports, definisce un membro di ancoraggio e un membro ricorsivo.

Il membro di ancoraggio restituisce il set di risultati di T0base . Si tratta del dipendente più alto della società. Ovvero, un dipendente che non segnala a un manager.

Ecco il set di risultati restituito dal membro di ancoraggio:

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

Il membro ricorsivo restituisce i subordinati diretti del dipendente nel set di risultati del membro di ancoraggio. Questa operazione viene ottenuta tramite un'operazione di join tra la tabella Employee e il DirectReports CTE. Si tratta di questo riferimento al CTE stesso che stabilisce la chiamata ricorsiva. In base al dipendente in CTE DirectReports come input (Ti), il join (MyEmployees.ManagerID = DirectReports.EmployeeID) restituisce come output (Ti + 1), i dipendenti che hanno (Ti) come manager.

Di conseguenza, la prima iterazione del membro ricorsivo restituisce questo set di risultati:

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

Il membro ricorsivo viene attivato ripetutamente. La seconda iterazione del membro ricorsivo usa il set di risultati a riga singola nel passaggio 3 (contenente un EmployeeID di ) come valore di 273input e restituisce questo set di risultati:

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

La terza iterazione del membro ricorsivo usa il set di risultati precedente come valore di input e restituisce questo set di risultati:

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

Il set di risultati finale restituito dalla query in esecuzione è l'unione di tutti i set di risultati generati dall'ancoraggio e dai membri ricorsivi.

Il set di risultati è il seguente.

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

Contenuti correlati

  • Last updated on