Redgate Hub (Italiano)

Introdotto in SQL Server 2005, la Common Table Expression (CTE) è un set di risultati con nome temporaneo a cui è possibile fare riferimento all’interno di un’istruzione SELECT, INSERT, UPDATE o DELETE. È inoltre possibile utilizzare un CTE in un’istruzione CREATE VIEW, come parte della query SELECT della vista. Inoltre, a partire da SQL Server 2008, è possibile aggiungere un CTE alla nuova istruzione MERGE.

SQL Server supporta due tipi di CTES: ricorsivo e non ricorsivo. In questo articolo, spiego come creare entrambi i tipi., Gli esempi forniti si basano su un’istanza locale di SQL Server 2008 e recuperano i dati dal database di esempio AdventureWorks2008.

Lavorare con espressioni di tabella comuni

Si definiscono CTE aggiungendo una clausola WITH direttamente prima dell’istruzione SELECT, INSERT, UPDATE, DELETE o MERGE.,”>

1 2 3 4 5

] <common_table_expression>::= cte_name )] AS (cte_query)

…which can be represented like this…

As you can see, if you include more than one CTE in your WITH clause, you must separate them with commas., Inoltre, per ogni CTE, è necessario fornire un nome, la parola chiave AS e un’istruzione SELECT. È inoltre possibile fornire nomi di colonne (separati da virgole), purché il numero di nomi corrisponda al numero di colonne restituite dal set di risultati.

L’istruzione SELECT nella query CTE deve seguire gli stessi requisiti utilizzati per la creazione di una vista. Per dettagli su tali requisiti, vedere l’argomento “CREA VISTA (Transact-SQL)” in SQL Server Books Online. Per ulteriori dettagli su CTEs in generale, vedere l’argomento ” CON common_table_expression (Transact-SQL).,”

Dopo aver definito la clausola WITH con i CTE necessari, puoi quindi fare riferimento a quei CTE come faresti con qualsiasi altra tabella. Tuttavia, è possibile fare riferimento a un CTE solo nell’ambito di esecuzione dell’istruzione che segue immediatamente la clausola WITH. Dopo aver eseguito l’istruzione, il set di risultati CTE non è disponibile per altre istruzioni.

Creazione di un’espressione di tabella comune non corsiva

Un CTE non corsivo è uno che non fa riferimento a se stesso all’interno del CTE. I CTES non ricorsivi tendono ad essere più semplici dei CTES ricorsivi, motivo per cui sto iniziando con questo tipo.,cteTotalSales:

Dopo aver specificato il nome CTE, fornisco due nomi di colonne, SalesPersonID e NetSales, che sono racchiusi tra parentesi e separati da una virgola. Ciò significa che il set di risultati restituito dalla query CTE deve restituire due colonne.

Successivamente, fornisco la parola chiave AS, quindi un insieme di parentesi che racchiudono la query CTE. In questo caso, l’istruzione SELECT restituisce il totale delle vendite per ogni persona di vendita (vendite totali raggruppate per ID venditore)., Come si può vedere, la query CTE può includere funzioni Transact-SQL, clausole GROUP BY o qualsiasi elemento che l’istruzione SELECT in una definizione di vista può includere.

Ora posso fare riferimento a cteTotalSales nella dichiarazione che segue immediatamente. Per questo esempio, creo un’istruzione SELECT che si unisce alle vendite.Vista vSalesPerson a cteTotalSales, in base all’ID venditore. Quindi estraggo i nomi e le posizioni dalla vista e le vendite nette dal CTE. La seguente tabella mostra i risultati restituiti da questa dichiarazione.,

Come hai visto in precedenza nella sintassi, puoi includere più CTE in una clausola WITH.,

Il primo CTE-cteTotalSales-è simile a quello dell’esempio precedente, tranne per il fatto che la clausola WHERE è stata ulteriormente qualificata per includere le vendite solo dal 2003. Dopo aver definito cteTotalSales, aggiungo una virgola e quindi definisco cteTargetDiff, che calcola la differenza tra il totale delle vendite e la quota di vendita.

La nuova definizione CTE specifica tre colonne per il set di risultati: SalesPersonID, SalesQuota e QuotaDiff., Come ci si aspetterebbe, la query CTE restituisce tre colonne. Il primo è l’ID venditore. Il secondo è la quota di vendita. Tuttavia, poiché una quota di vendita non è definita per alcuni venditori, utilizzo un’istruzione CASE. Se il valore è null, tale valore è impostato su 0, altrimenti viene utilizzato il valore SalesQuota effettivo.

La colonna finale restituita è la differenza tra le vendite nette e la quota di vendita. Di nuovo, uso una dichiarazione del CASO. Se il valore SalesQuota è nullo, viene utilizzato il valore NetSales, altrimenti la quota di vendita viene sottratta dalle vendite nette per arrivare alla differenza.,

Qualcosa di interessante da notare sulla seconda query CTE è che ho aderito alle vendite.Tabella venditore al primo CTE-cteTotalSales – così ho potuto calcolare la differenza tra le vendite totali e la quota di vendita. Ogni volta che si definiscono più CTE in una singola clausola WITH, è possibile fare riferimento a CTE precedenti (ma non viceversa).

Una volta definiti i miei CTE, posso fare riferimento a loro nella prima istruzione che segue il CTE, come hai visto nell’esempio precedente. In questo caso, mi unisco alle vendite.,vSalesPerson visualizza cteTotalSales e quindi partecipa a cteTargetDiff, tutti basati sull’ID venditore. Il mio elenco di selezione include quindi colonne da tutte e tre le fonti. L’istruzione restituisce i risultati mostrati nella tabella seguente.

Come puoi vedere, i dati di vendita sono forniti per tutti i venditori, inclusa la città in cui risiedono, le loro vendite nette, la loro quota di vendita e la differenza calcolata tra le due cifre. In questo caso, tutti superano bene la quota, dove è stata definita una quota.,

Creazione di un’espressione di tabella comune ricorsiva

Un CTE ricorsivo è uno che fa riferimento a se stesso all’interno di quel CTE. Il CTE ricorsivo è utile quando si lavora con dati gerarchici perché il CTE continua a essere eseguito fino a quando la query non restituisce l’intera gerarchia.

Un tipico esempio di dati gerarchici è una tabella che include un elenco di dipendenti. Per ogni dipendente, la tabella fornisce un riferimento al manager di quella persona. Tale riferimento è di per sé un ID dipendente all’interno della stessa tabella., È possibile utilizzare un CTE ricorsivo per visualizzare la gerarchia dei dati dei dipendenti, come appare all’interno dell’organigramma.

Si noti che un CTE creato in modo errato potrebbe entrare in un ciclo infinito. Per evitare ciò, è possibile includere il suggerimento MAXRECURSION nella clausola OPTION dell’istruzione primaria SELECT, INSERT, UPDATE, DELETE o MERGE. Per informazioni sull’utilizzo dei suggerimenti di query, vedere l’argomento “Suggerimenti di query (Transact-SQL)” in SQL Server Books Online.,

Per dimostrare come funziona il CTE ricorsivo, ho usato le seguenti istruzioni Transact-SQL per creare e popolare la tabella Employees nel database AdventureWorks2008:

Come potresti capire, il database AdventureWorks2008 include già le risorse umane.Tabella dei dipendenti. Tuttavia, tale tabella ora utilizza il tipo di dati hierarchyid per memorizzare i dati gerarchici, il che introdurrebbe una complessità non necessaria quando si tenta di dimostrare un CTE ricorsivo. Per questo motivo, ho creato il mio tavolo., Tuttavia, se si desidera provare un CTE ricorsivo senza creare e compilare una nuova tabella, è possibile utilizzare il database di esempio AdventureWorks fornito con SQL Server 2005. Le risorse umane.La tabella dei dipendenti in quel database memorizza i dati in modo simile alla tabella che creo sopra.,div>2

Come puoi vedere, il CTE restituisce cinque colonne: EmpID, FirstName, LastName, MgrID e EmpLevel. La colonna EmpLevel si riferisce al livello nella gerarchia in cui i dipendenti si adattano. Il livello più alto della gerarchia è 1, il livello successivo è 2, seguito da 3 e così via.

La query CTE è a sua volta composta da due istruzioni SELECT, collegate all’operatore UNION ALL., Una query CTE ricorsiva deve contenere almeno due membri (istruzioni), collegati dall’operatore UNION ALL, UNION, INTERSECT o EXCEPT. In questo esempio, la prima istruzione SELECT è il membro di ancoraggio e la seconda istruzione è il membro ricorsivo. Tutti i membri di ancoraggio devono precedere i membri ricorsivi e solo i membri ricorsivi possono fare riferimento al CTE stesso. Inoltre, tutti i membri devono restituire lo stesso numero di colonne con i tipi di dati corrispondenti.

Ora diamo un’occhiata più da vicino alle dichiarazioni stesse., La prima istruzione, il membro di ancoraggio, recupera l’ID dipendente, nome, cognome e ID gestore dalla tabella Dipendenti, in cui l’ID gestore è null. Questo sarebbe il dipendente in cima alla gerarchia, il che significa che questa persona non riferisce a nessuno. Di conseguenza, il valore ID manager è null. Per riflettere che questa persona è in cima alla gerarchia, assegno un valore di 1 alla colonna EmpLevel.

La seconda istruzione nella query CTE-il membro ricorsivo-recupera anche l’ID dipendente, il nome, il cognome e l’ID manager per i dipendenti nella tabella Dipendenti., Tuttavia, si noti che mi unisco alla tabella Dipendenti al CTE stesso. Inoltre, il join si basa sull’ID manager nella tabella Dipendenti e sull’ID dipendente nel CTE. In questo modo, il CTE scorrerà la tabella Employees fino a quando non restituirà l’intera gerarchia.

Un altro elemento da notare sulla seconda istruzione è che, per la colonna EmpLevel, aggiungo il valore 1 al valore EmpLevel come appare nel CTE. In questo modo, ogni volta che l’istruzione attraversa la gerarchia, il livello successivo corretto viene applicato ai dipendenti del livello.,

Dopo aver definito la mia clausola WITH, creo un’istruzione SELECT che recupera i dati dal CTE. Si noti, tuttavia, che per la colonna Manager, recupero il nome e il cognome del dipendente associato all’ID manager nel CTE. Questo mi permette di visualizzare il nome completo del manager per ogni dipendente. La tabella seguente mostra il set di risultati restituito dall’istruzione SELECT e dal relativo CTE.,

Come puoi vedere, il CTE, sia ricorsivo che non, può essere uno strumento utile quando devi generare set di risultati temporanei a cui puoi accedere in un’istruzione SELECT, INSERT, UPDATE, DELETE o MERGE. In un certo senso, un CTE è come una tabella derivata: non è memorizzato come oggetto ed è valido solo durante l’esecuzione dell’istruzione primaria. Tuttavia, a differenza della tabella derivata, un CTE può essere referenziato più volte all’interno di una query e può essere autoreferenziale. E meglio di tutti, i CTE sono relativamente facili da implementare.,

Avrai notato che Bob sta usando AdventureWorks2008 piuttosto che AdventureWorks. Se si preferisce eseguire questi esempi nel database AdventureWorks anziché nel database AdventureWorks2008, è necessario modificare la colonna BusinessEntityID nella colonna SalesPersonID.