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
Vedere anche
Altre risorse
WITH common_table_expression (Transact-SQL)
SELECT (Transact-SQL)
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
DELETE (Transact-SQL)
CREATE VIEW (Transact-SQL)