WITH common_table_expression (Transact-SQL)

Indica un set di risultati denominato temporaneo, noto come espressione di tabella comune (CTE). L'espressione di tabella comune è derivata da una query semplice e definita all'interno dell'ambito di esecuzione di un'istruzione SELECT, INSERT, UPDATE o DELETE. Questa clausola può anche essere utilizzata in un'istruzione CREATE VIEW come parte dell'istruzione di definizione SELECT. Un'espressione di tabella comune può includere riferimenti a se stessa. In questo caso viene indicata con il nome di espressione di tabella comune ricorsiva.

Convenzioni della sintassi Transact-SQL

Sintassi

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

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

Argomenti

• expression_name
  Identificatore valido per l'espressione di tabella comune. Il nome specificato in expression_name deve essere diverso dal nome di qualsiasi altra espressione di tabella comune definita nella stessa clausola WITH <common_table_expression>, ma expression_name può corrispondere al nome di una vista o tabella di base. Tutti i riferimenti a expression_name nella query utilizzano l'espressione di tabella comune e non l'oggetto di base.

• column_name
  Specifica un nome di colonna nell'espressione di tabella comune. Non sono consentiti nomi duplicati all'interno di una singola definizione CTE. Il numero dei nomi di colonna specificato deve corrispondere al numero delle colonne nel set dei risultati di CTE_query_definition. L'elenco dei nomi di colonna è facoltativo solo se i nomi distinti di tutte le colonne risultanti sono specificati nella definizione della query.

• CTE_query_definition
  Specifica un'istruzione SELECT il cui set di risultati popola l'espressione di tabella comune. L'istruzione SELECT per CTE_query_definition deve soddisfare gli stessi requisiti necessari per creare una vista, con la differenza che una CTE non può definire un'altra CTE. Per ulteriori informazioni, vedere la sezione Osservazioni e CREATE VIEW (Transact-SQL).

  Se si definiscono più query CTE CTE_query_definition, le definizioni di query devono essere unite in join da uno dei seguenti operatori sugli insiemi: UNION ALL, UNION, EXCEPT o INTERSECT. Per ulteriori informazioni sull'utilizzo di definizioni di query CTE ricorsive, vedere la sezione "Osservazioni" seguente e l'argomento Query ricorsive tramite espressioni di tabella comuni.

Osservazioni

Linee guida per la creazione e l'utilizzo di CTE

Le linee guida seguenti sono valide per le CTE non ricorsive. Per le linee guida relative alle CTE ricorsive, vedere "Linee guida per la definizione e l'utilizzo di espressioni CTE ricorsive" più sotto.

• Una CTE deve essere seguita da un'istruzione SELECT, INSERT, UPDATE o DELETE che faccia riferimento ad alcune o a tutte le colonne CTE. Un'espressione CTE può anche essere specificata in un'istruzione CREATE VIEW come parte dell'istruzione di definizione SELECT della vista.
• In una CTE non ricorsiva è possibile definire più query CTE. Le definizioni devono essere combinate da uno di questi operatori sugli insiemi: UNION ALL, UNION, INTERSECT o EXCEPT.
• Una CTE può far riferimento a se stessa e alle CTE definite in precedenza nella stessa clausola WITH. Il riferimento in avanti non è consentito.
• Non è consentito specificare più di una clausola WITH in una CTE. Ad esempio, se una definizione CTE_query_definition include una subquery, tale subquery non può includere una clausola WITH nidificata che definisce un'altra CTE.
• Le clausole seguenti non possono essere utilizzate in CTE_query_definition:
  • COMPUTE o COMPUTE BY
  • ORDER BY (tranne quando si specifica una clausola TOP)
  • INTO
  • Clausola OPTION con hint per la query
  • FOR XML
  • FOR BROWSE
• Quando un'espressione CTE viene usata in un'istruzione che fa parte di un batch, l'istruzione precedente deve essere seguita da un punto e virgola.
• Una query che fa riferimento a un'espressione CTE può essere utilizzata per definire un cursore.
• L'espressione CTE può fare riferimento alle tabelle nei server remoti.
• Durante l'esecuzione di una CTE, tutti gli hint che fanno riferimento a una CTE possono entrare in conflitto con altri hint individuati quando la CTE accede alle tabelle sottostanti, allo stesso modo degli hint che fanno riferimento alle viste nelle query. In questo caso, la query restituisce un errore. Per ulteriori informazioni, vedere Risoluzione delle viste.

Linee guida per la definizione e l'utilizzo di CTE ricorsive.

Le guide linea seguenti sono valide per la definizione di un'espressione CTE ricorsiva.

• La definizione CTE ricorsiva deve contenere almeno due definizioni di query CTE, un membro non ricorsivo e un membro ricorsivo. È possibile definire più membri ricorsivi e non ricorsivi, ma tutte le definizioni delle query dei membri non ricorsivi devono precedere la definizione del primo membro ricorsivo. Tutte le definizioni delle query CTE **sono membri non ricorsivi tranne nei casi un cui fanno riferimento all'espressione CTE stessa.
• I membri non ricorsivi devono essere combinati da uno degli operatori sugli insiemi seguenti: UNION ALL, UNION, INTERSECT o EXCEPT. UNION ALL è l'unico operatore sugli insiemi consentito tra l'ultimo membro non ricorsivo e il primo, nonché durante la combinazione di più membri ricorsivi.
• Il numero delle colonne nei membri ricorsivi e non ricorsivi deve essere lo stesso.
• Il tipo di dati di una colonna nel membro ricorsivo deve essere lo stesso del tipo di dati della colonna corrispondente nel membro non ricorsivo.
• La clausola FROM di un membro ricorsivo deve fare riferimento solo una volta all'espressione CTE expression_name.
• I seguenti elementi non sono consentiti nella definizione CTE_query_definition di un membro ricorsivo:
  • SELECT DISTINCT
  • GROUP BY
  • HAVING
  • Aggregazione scalare
  • TOP
  • LEFT, RIGHT, OUTER JOIN (INNER JOIN è consentito)
  • Subquery:
  • Hint applicato a un riferimento ricorsivo a un'espressione CTE all'interno di una definizione CTE_query_definition.

Le guide linea seguenti sono valide per l'utilizzo di una CTE ricorsiva:

• Tutte le colonne restituite dalla CTE ricorsiva supportano valori Null a prescindere dal supporto dei valori Null delle colonne restituite dalle istruzioni SELECT coinvolte.
• Un CTE ricorsiva formulata in modo non corretto può provocare 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. Per evitare un ciclo infinito, è possibile limitare il numero di livelli di ricorsione consentito per una particolare espressione utilizzando l'hint MAXRECURSION e un valore compreso tra 0 e 32.767 nella clausola OPTION dell'istruzione INSERT, UPDATE, DELETE o SELECT. Ciò consente di controllare l'esecuzione dell'istruzione fino a quando non viene risolto il problema relativo al codice che sta creando il ciclo. Il valore predefinito per l'intero server è 100. Se è specificato 0, non viene applicato alcun limite. È possibile specificare solo un valore MAXRECURSION per istruzione. Per ulteriori informazioni, vedere query_hint (Transact-SQL).
• Non è possibile utilizzare una vista che contiene un'espressione di tabella comune ricorsiva per aggiornare i dati.
• I cursori possono essere definiti sulle query tramite CTE. La CTE corrisponde all'argomento select_statement che definisce il set di risultati del cursore. Sono consentiti solo i cursori fast forward only e statici (snapshot) per le CTE ricorsive. Se viene specificato un altro tipo di cursore in una CTE ricorsiva, il tipo di cursore viene convertito in statico.
• Nelle CTE è possibile far riferimento alle tabelle nei server remoti. Se nel membro ricorsivo della CTE si fa riferimento al server remoto, viene creato uno spool per ogni tabella remota in maniera che si possa accedere alle tabelle in modo locale ripetutamente.

Esempi

A. Creazione di un'espressione di tabella comune semplice

Nell'esempio seguente viene illustrato il numero di dipendenti che sono subordinati direttamente a ogni manager in Adventure Works Cycles.

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) 
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
SELECT ManagerID, DirectReports 
FROM DirReps 
ORDER BY ManagerID;
GO

B. Utilizzo di un'espressione di tabella comune per limitare il numero medio dei subordinati

Nell'esempio seguente viene illustrato il numero medio di dipendenti subordinati ai manager.

WITH DirReps (Manager, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) AS DirectReports
    FROM HumanResources.Employee
    GROUP BY ManagerID
) 
SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
FROM DirReps 
WHERE DirectReports>= 2 ;
GO

C. Più riferimenti relativi a un'espressione di tabella comune

Nell'esempio seguente viene illustrato il numero totale di ordini di vendita e la data di ordine di vendita più recente nella tabella SalesOrderHeader per ogni venditore. Nell'istruzione in esecuzione, viene fatto riferimento alla CTE due volte: una volta per restituire le colonne selezionate per il venditore, e successivamente per recuperare dettagli simili per il manager del venditore. I dati per il venditore e il manager vengono restituiti in una riga singola.

USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

D. Utilizzo di un'espressione di tabella comune ricorsiva per visualizzare più livelli di ricorsione

Nell'esempio seguente viene illustrato l'elenco gerarchico dei manager e dei dipendenti a loro subordinati.

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports ;
GO

E. Utilizzo di un'espressione di tabella comune ricorsiva per visualizzare due livelli di ricorsione

Nell'esempio seguente vengono illustrati i manager e i dipendenti che sono loro subordinati. Il numero di livelli restituiti è limitato a due.

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports 
WHERE EmployeeLevel <= 2 ;
GO

F. Utilizzo di un'espressione di tabella comune ricorsiva per visualizzare un elenco gerarchico

Nell'esempio seguente viene utilizzato come base l'esempio C aggiungendo i nomi del manager e dei dipendenti e i loro rispettivi titoli. La gerarchia dei manager e dei dipendenti viene inoltre evidenziata rientrando ogni livello.

USE AdventureWorks;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
    FROM HumanResources.Employee as e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports 
ORDER BY Sort;
GO

G. Utilizzo di MAXRECURSION per annullare un'istruzione

È possibile utilizzare MAXRECURSION per impedire che una CTE ricorsiva non corretta provochi un ciclo infinito. Nell'esempio seguente viene creato intenzionalmente un ciclo infinito e viene utilizzato l'hint MAXRECURSION per limitare a due il numero di livelli di ricorsione.

USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee 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);
GO

Dopo la correzione dell'errore del codice, MAXRECURSION non è più necessario. Nell'esempio seguente viene illustrato il codice corretto.

USE AdventureWorks;
GO
WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM HumanResources.Employee AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

H. Utilizzo di un'espressione di tabella comune per analizzare in maniera selettiva una relazione ricorsiva in un'istruzione SELECT.

Nell'esempio seguente viene illustrata la gerarchia di assembly e componenti del prodotto che sono necessari per costruire la bicicletta per ProductAssemblyID = 800.

USE AdventureWorks;
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;
GO

I. Utilizzo di una CTE ricorsiva in un'istruzione UPDATE

Nell'esempio seguente il valore VacationHours viene aggiornato del 25% per tutti i dipendenti che sono subordinati direttamente o indirettamente a ManagerID 12. L'espressione di tabella comune restituisce un elenco gerarchico di dipendenti che sono direttamente subordinati a ManagerID 12 e dei dipendenti che sono subordinati a tali dipendenti, e così via. Vengono modificate solo le righe restituite dall'espressione di tabella comune.

USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO

J. Utilizzo di membri ricorsivi e non ricorsivi

Nell'esempio seguente vengono utilizzati più membri ricorsivi e non ricorsivi per restituire tutti gli antenati di una specifica persona. Viene creata una tabella e vengono inseriti i valori per stabilire l'albero genealogico restituito dalla CTE ricorsiva.

-- Genealogy table
IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person;
GO
CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT Person VALUES(1, 'Sue', NULL, NULL);
INSERT Person VALUES(2, 'Ed', NULL, NULL);
INSERT Person VALUES(3, 'Emma', 1, 2);
INSERT Person VALUES(4, 'Jack', 1, 2);
INSERT Person VALUES(5, 'Jane', NULL, NULL);
INSERT Person VALUES(6, 'Bonnie', 5, 4);
INSERT Person VALUES(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 Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father 
    FROM 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, Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ID = Person.ID;
GO

Vedere anche

Riferimento

CREATE VIEW (Transact-SQL)
DELETE (Transact-SQL)
EXCEPT e INTERSECT (Transact-SQL)
INSERT (Transact-SQL)
SELECT (Transact-SQL)
UPDATE (Transact-SQL)

Altre risorse

Query ricorsive tramite espressioni di tabella comuni
Utilizzo delle espressioni di tabella comuni

Guida in linea e informazioni

Assistenza su SQL Server 2005