SQLShack (Italiano)

Cos’è un’espressione di tabella comune

Un’espressione di tabella comune, chiamata anche CTE in breve, è un set di risultati con nome temporaneo a cui è possibile fare riferimento all’interno di un’istruzione SELECT, INSERT, UPDATE o DELETE. Il CTE può essere utilizzato anche in una vista.

In questo articolo, vedremo in dettaglio come creare e utilizzare CTEs dal nostro SQL Server.,

Sintassi ed esempi per espressioni di tabella comuni

La query CTE inizia con un “Con” ed è seguita dal nome dell’espressione. Useremo questo nome di espressione nella nostra query select per visualizzare il risultato della nostra query CTE e scrivere la nostra definizione di query CTE.,

To view the CTE result we use a Select query with the CTE expression name.,r

Common Table Expression (CTE) Types

There are two types of CTEs: Recursive and Non-Recursive

Non-Recursive CTEs

Non-Recursive CTEs are simple where the CTE doesn’t use any recursion, or repeated processing in of a sub-routine., Creeremo un semplice CTE non ricorsivo per visualizzare il numero di riga da 1 a 10.

Secondo la sintassi CTE ogni query CTE inizierà con un “Con” seguito dal nome dell’espressione CTE con l’elenco delle colonne.

Qui abbiamo usato solo una colonna come ROWNO. La prossima è la parte di query, qui scriviamo la nostra query select da eseguire per il nostro CTE. Dopo aver creato la nostra query CTE per eseguire il CTE utilizzare l’istruzione select con il nome dell’espressione CTE.

Output: quando eseguiamo la query, possiamo vedere l’output sottostante.,

CTE ricorsive

I CTE ricorsivi utilizzano cicli procedurali ripetuti aka ricorsione. La query ricorsiva si chiama fino a quando la query ha soddisfatto la condizione. In un CTE ricorsivo dovremmo fornire una condizione where per terminare la ricorsione.:

Vedremo come creare una semplice query ricorsiva per visualizzare il numero di riga da 1 a 10 utilizzando un CTE.

In primo luogo dichiariamo la variabile intera come “RowNo” e impostiamo il valore predefinito come 1 e abbiamo creato la nostra prima query CTE come nome di espressione, “ROWCTE”., Nel nostro CTE mostreremo prima il numero di riga predefinito e successivamente useremo un Union ALL per incrementare e visualizzare il numero di riga 1 di uno fino a quando la riga No raggiunge il valore incrementato a 10. Per visualizzare il risultato, useremo una query select per visualizzare il nostro risultato CTE.,/div>

Uscita: Quando si esegue la query, possiamo vedere il seguente output.,

Query CTE per visualizzare l’intervallo di date:

Consideriamo come esiste uno scenario per visualizzare la data dalla data di inizio alla data di fine tutti uno per uno come ogni riga con i dettagli. Per visualizzare i dati ricorsivi, utilizzeremo la query CTE.

Qui scriveremo una query CTE per visualizzare l’intervallo di date con il numero della settimana e il giorno. Per questo impostiamo la data di inizio e di fine nel parametro. Qui in questo esempio abbiamo usato getdate () per impostare la data di inizio come data di oggi, e per la data di fine aggiungiamo 16 giorni da oggi.,

CTE senza Union All

Qui possiamo vedere che abbiamo creato una semplice query CTE per visualizzare il numero RowNo, data di inizio e settimana. Quando eseguiamo questo otterremo un solo risultato con RowNo come “1”, StartDate come data corrente e numero della settimana insieme al giorno della settimana.

Output: quando eseguiamo la query, possiamo vedere l’output sottostante.,

CTE con l’Unione di Tutti

per visualizzare il risultato dalla data di inizio alla data di fine uno come ricorsiva, si usa una Unione per l’incremento RowNo, aggiungere il giorno, uno per uno, fino a che la condizione soddisfatto l’intervallo di date, al fine di interrompere la ricorsione, abbiamo bisogno di impostare alcune condizioni. In questo esempio, ripetiamo la ricorsione per visualizzare i nostri record fino a quando la data è minore o uguale alla data di fine.

Output: quando eseguiamo la query, possiamo vedere l’output sottostante.,

CTE multiple

In alcuni scenari, abbiamo bisogno di creare più di una query CTE e unirsi a loro per visualizzare il nostro risultato. In questo caso, possiamo usare i CTE multipli. Possiamo creare una query CTE multipla e combinarle in un’unica query utilizzando la virgola. CTE multipli devono essere separati da”, ” virgola maggese dal nome CTE.

Useremo sopra lo stesso esempio di intervallo di date per utilizzare più di una query CTE, qui possiamo vedere come abbiamo creato due query CTE come CTE1 e CTE 2 per visualizzare il risultato dell’intervallo di date sia per CTE1 che per CTE2.,

Esempio:

Output: Quando eseguiamo la query, possiamo vedere l’output sottostante.

Utilizzando query CTE per la tabella SQL

Ora vediamo su, come utilizzare query CTE per i nostri dati della tabella SQL server.,

Create Database: in Primo luogo, dobbiamo creare un database per creare la tabella

Create Table: Ora è possibile creare un esempio di Elemento di Tabella sul Database creato.

Inserisci dati di esempio: inseriremo alcuni record di esempio per l’utilizzo nella nostra query CTE.,

Esempio CTE:

Ora creeremo un semplice risultato temporaneo usando la query CTE. Qui in questa query CTE abbiamo dato il nome dell’espressione come “itemCTE” e abbiamo aggiunto l’elenco di colonne che usiamo nella query CTE. Nella query CTE visualizziamo tutti i dettagli dell’articolo con l’anno.

Output: quando eseguiamo la query, possiamo vedere l’output sottostante.

CTE usando Union ALL

Consideriamo che ci sono sotto due scenari per visualizzare il risultato.

1. Il primo scenario è quello di visualizzare ogni articolo Prezzo dell’anno in corso.,
2. Il secondo scenario è quello di aumentare del 10% per ogni articolo Prezzo per il prossimo anno.

Per questo usiamo la query CTE sopra. In questa query, aggiungiamo l’UNIONE ALL e in UNION ALL Query facciamo il calcolo per aggiungere il 10% a ciascun prezzo dell’articolo e mostrare nella riga successiva con l’aggiunta di un anno.

Output: quando eseguiamo la query, possiamo vedere l’output sottostante.

Common Table Expressions (CTE) for Insert

Ora vedremo come inserire il risultato CTE in un’altra tabella. Per questo consideriamo la nostra tabella degli articoli sopra., Inseriamo i dettagli articolo risultato di sopra query CTE alla tabella di cronologia degli elementi. Per questo prima creiamo una tabella di cronologia degli elementi.

Crea tabella cronologia articoli: in questa tabella cronologia, aggiungiamo le stesse colonne della tabella articoli insieme alla colonna MarketRate come prezzo articolo presente o futuro. Ecco la query per creare una tabella ItemHistory.

CTE Insert Example:

Qui usiamo sopra la stessa query CTE Inserire il risultato nella tabella della cronologia degli elementi. Da questa query inseriamo entrambi i dettagli dell’articolo del prezzo dell’articolo dell’anno presente insieme ai prezzi dell’articolo dell’anno successivo aggiunti come 10% in più.,

Output: quando eseguiamo la query, possiamo vedere l’output di seguito come 30 record sono stati inseriti nella nostra tabella Cronologia articoli.

Seleziona Query:

Per visualizzare il risultato della cronologia degli elementi selezioniamo e visualizziamo tutti i dettagli.,

Uscita: Quando si esegue la query, possiamo vedere il seguente output dalla voce di tabella di storia.

Crea vista con CTE Esempio:

Ora vediamo come utilizzare la query CTE sopra può essere utilizzato in una vista., Qui creiamo una vista e aggiungiamo il risultato CTE all’interno della vista. Quando selezioniamo la vista come risultato, possiamo vedere l’output CTE verrà visualizzato.

Example Query:

Output: Quando eseguiamo la query, possiamo vedere l’output sottostante come risultato dalla Vista.

Come scrivere una query CTE pulita:

Ecco alcune linee guida di base che devono essere seguite per scrivere una buona query CTE.

1. Un CTE deve essere seguito da una singola istruzione SELECT, INSERT, UPDATE o DELETE che fa riferimento ad alcune o tutte le colonne CTE.,
2. È possibile definire più definizioni di query CTE in un CTE non ricorsivo.
3. Un CTE può fare riferimento a se stesso e ai CTE precedentemente definiti nella stessa clausola WITH
4. Possiamo usare solo una Clausola With in una CTE
5. ORDER BY, INTO, COMPUTE o COMPUTE BY, OPTION, FOR XML cannot be used in non-ricorsive CTE query definition
6. SELECT DISTINCT, GROUP BY, HAVING, Scalar aggregation, TOP, LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed) subqueries cannot be used in a ricorsive CTE query definition.,

Conclusione

CTEs può essere utilizzato per creare una query ricorsiva e può essere utilizzato per fare riferimento a se stesso più volte. CTE può essere utilizzato al posto delle viste e infine un CTE è facile e semplice per la leggibilità e la manutenibilità del codice.

• Autore
• Post Recenti

Syed Shanu è un Microsoft MVP, due CsharpCorner MVP e due di Codice in fase di progetto MVP, Autore, Blogger, Speaker e sempre felice di condividere ciò che sa di altri.,
Lui è fondamentalmente da Madurai, Tamil Nadu, India. Sta lavorando come responsabile tecnico in Corea del Sud con più di 10 anni di esperienza sulle tecnologie Microsoft.
Shanu è attivo nella comunità e sempre felice di condividere argomenti relativi a ASP.NET, MVC, ASP.NET Il nostro sito utilizza cookie tecnici e di terze parti. Ha scritto più di 70 articoli con varie tecnologie ASP.NET, SQL Server, C#, AngularJS, Angular2, ASP.NET Core, Unity 3D, applicazione universale di Windows e sviluppo di app Samsung Gear., È più volte vincitore di TechNet Guru Gold e puoi trovare tutto il suo contributo in MSDN e anche in TechNet Wiki https://social.technet.microsoft.com/profile/syedshanu/
È anche autore dell’eBook” Query SQL per principianti”puoi scaricare l’eBook da qui: http://www.c-sharpcorner.com/ebooks/sql-queries-for-beginners.,
Visualizza tutti i messaggi di Syed Shanu

Ultimi messaggi di Syed Shanu (vedi tutti)

• SQL Server Espressioni di Tabella Comuni (CTE) – febbraio 23, 2017
• Creazione di una “smart” trigger basato audit trail per SQL Server – 12 dicembre, 2016