Condividi tramite


Utilizzo delle espressioni di tabella comuni

Un'espressione di tabella comune (CTE) può essere considerata un set di risultati temporaneo definito nell'ambito di esecuzione di una singola istruzione SELECT, INSERT, UPDATE, DELETE o CREATE VIEW. Una CTE è simile a una tabella derivata poiché non è archiviata come un oggetto e viene mantenuta solo per la durata della query. A differenza di una tabella derivata, una CTE può essere autoreferenziale e vi si può fare riferimento più volte all'interno della stessa query.

È possibile utilizzare le CTE per:

  • Creare una query ricorsiva. Per ulteriori informazioni, vedere Query ricorsive tramite espressioni di tabella comuni.

  • Sostituire una vista quando non ne è richiesto un utilizzo generale, ovvero non è necessario archiviare la definizione nei metadati.

  • Abilitare il raggruppamento in base a una colonna derivata da un'istruzione di selezione secondaria scalare o a una funzione non deterministica o che ha accesso esterno.

  • Fare riferimento alla tabella risultante più volte nella stessa istruzione.

L'utilizzo di una CTE ha il vantaggio di una maggiore leggibilità e facilità nella gestione di query complesse. È possibile dividere la query in blocchi di generazione logici separati e più semplici. È possibile quindi utilizzare questi blocchi semplici per costruire CTE provvisorie più complesse finché non viene generato il set di risultati finale.

È possibile definire CTE all'interno di routine definite dall'utente, quali funzioni, stored procedure, trigger o viste.

Struttura di una CTE

Una CTE è costituita da un nome di espressione che rappresenta la CTE, un elenco di colonne facoltativo e una query che definisce la CTE. Dopo che è stata definita, è possibile fare riferimento a una CTE in un'istruzione SELECT, INSERT, UPDATE o DELETE allo stesso modo di una tabella o vista. È inoltre possibile utilizzare le CTE nelle istruzioni CREATE VIEW come parte della corrispondente istruzione SELECT di definizione.

Di seguito viene illustrata la struttura della sintassi di base di una CTE:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

L'elenco di nomi di colonne è facoltativo solo se nella definizione della query vengono specificati nomi distinti per tutte le colonne risultanti.

Di seguito viene riportata l'istruzione per eseguire la CTE:

SELECT <column_list>

FROM expression_name;

Esempio

Nell'esempio seguente vengono mostrati i componenti della struttura della CTE, ovvero nome dell'espressione, elenco di colonne e query. L'espressione CTE Sales_CTE presenta tre colonne (SalesPersonID, NumberOfOrders e MaxDate) ed è definita come numero totale di ordini di vendita e data dell'ordine di vendita più recente nella tabella SalesOrderHeader di tutti i venditori. Durante l'esecuzione dell'istruzione viene fatto riferimento due volte alla CTE, la prima volta per restituire le colonne selezionate per il venditore e la seconda volta per recuperare dettagli simili relativi al direttore del venditore. I dati relativi sia al venditore che al direttore vengono restituiti in una singola riga.

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

Quello che segue è un set dei risultati parziale:

EmployeeID  NumberOfOrders MaxDate  ManagerID NumberOfOrders MaxDate
----------- -------------- ---------- --------- -------------- ----------
268         48             2004-06-01 273       NULL           NULL
275         450            2004-06-01 268       48             2004-06-01
276         418            2004-06-01 268       48             2004-06-01
277         473            2004-06-01 268       48             2004-06-01