Clausola GROUP BY(istruzione SELECT) consente di raggruppare i dati (righe) in base al valore di una colonna o più colonne o espressioni. Il risultato sarà un insieme di righe di riepilogo.

Ogni colonna nell'elenco di selezione deve essere presente nella clausola GROUP BY, le uniche eccezioni sono costanti e colonne - operandi di funzioni aggregate.

Una tabella può essere raggruppata in base a qualsiasi combinazione delle sue colonne.

Funzioni aggregate vengono utilizzati per ottenere un singolo valore totale da un gruppo di righe. Tutte le funzioni aggregate eseguono calcoli su un singolo argomento, che può essere una colonna o un'espressione. Qualsiasi funzione aggregata restituisce un valore costante che viene visualizzato in una colonna separata nel risultato.

Le funzioni aggregate sono specificate nell'elenco di colonne di un'istruzione SELECT, che può anche contenere una clausola GROUP BY. Se l'istruzione SELECT non ha una clausola GROUP BY e l'elenco delle colonne select contiene, by almeno, una funzione aggregata, non dovrebbe contenere colonne semplici. D'altra parte, un elenco selezionato di colonne può contenere nomi di colonne che non sono argomenti per la funzione di aggregazione se tali colonne vengono utilizzate come argomenti per la clausola GROUP BY.

Se la query contiene una clausola WHERE, le funzioni aggregate calcolano un valore per i risultati della selezione.

Funzioni aggregate MIN e MAX calcolare il più piccolo e maggior valore colonna rispettivamente. Gli argomenti possono essere numeri, stringhe e date. Tutti i valori NULL vengono rimossi prima del calcolo (cioè, non presi in considerazione).

SOMMA aggregata funzione calcola importo totale valori di colonna. Solo i numeri possono essere argomenti. L'utilizzo del parametro DISTINCT rimuove eventuali valori duplicati nella colonna prima dell'applicazione SOMMA funzioni... Allo stesso modo, rimuovere tutti i valori NULL prima di applicare questa funzione di aggregazione.

Aggregato Funzione AVG restituisce la media di tutti i valori in una colonna. Gli argomenti possono anche essere solo numeri e tutti i valori NULL vengono rimossi prima della valutazione.

Funzione di aggregazione COUNT ha due diverse forme:

• COUNT (col_name) - conta il numero di valori nella colonna col_name, i valori NULL non vengono conteggiati
• COUNT (*) - conta il numero di righe nella tabella, vengono presi in considerazione anche i valori NULL

Se la query utilizza la parola chiave DISTINCT, tutti i valori di colonna duplicati vengono rimossi prima che venga utilizzata la funzione COUNT.

COUNT_BIG funzione stessa funzione di COUNT. L'unica differenza tra i due è il tipo di risultato che restituiscono: COUNT_BIG restituisce sempre valori BIGINT, mentre COUNT restituisce valori di dati INTEGER.

V AVERE un'offerta definisce una condizione che viene applicata a un gruppo di righe. Ha lo stesso significato per i gruppi di righe della clausola WHERE per il contenuto della tabella corrispondente (WHERE si applica prima del raggruppamento, HAVING dopo).

Può produrre generalizzato elaborazione in lotti valori di campo. Questo viene fatto utilizzando funzioni aggregate. Le funzioni aggregate producono un singolo valore per l'intero gruppo della tabella. SQL fornisce le seguenti funzioni aggregate:

• CONTARE- conta il numero di righe della tabella con valori non NULL del campo specificato come argomento.
• SOMMA- calcola la somma aritmetica di tutti i valori selezionati del campo dato.
• AVG- esegue la media di tutti i valori selezionati di questo campo.
• MAX- visualizza il valore più grande di tutti i valori selezionati per questo campo.
• MIN- Emette il valore più piccolo di tutti i valori selezionati per questo campo.

  Utilizzo di funzioni aggregate

  Le funzioni aggregate vengono utilizzate in modo simile ai nomi dei campi nella clausola SELECT di una query, con un'eccezione: prendono i nomi dei campi come argomenti. È possibile utilizzare solo campi numerici con SOMMA e AVG... INSIEME A CONTARE, MAX, e MIN possono essere utilizzati sia campi numerici che caratteri. Se utilizzato con i campi di caratteri MAX e MIN li tradurrà nell'equivalente ASCII. Significa che MIN sceglierà il primo e MAX l'ultimo valore in ordine alfabetico.

  Per trovare le vendite totali nella tabella delle vendite, dobbiamo scrivere la seguente query:

  SELEZIONA SOMMA (Sum) DA Vendite

  Di conseguenza, otteniamo:

  Questa query ha contato il numero di valori non vuoti nel campo SNum della tabella Sells. Se riscriviamo la richiesta come segue:

  SELEZIONA COUNT (SDate) FROM Sells

  Quindi come risultato otteniamo:

  COUNT OF SDate

  4

  Risultati di query diversi durante il calcolo apparentemente uguali si ottengono perché uno dei valori del campo SDate ha valore vuoto (NULLO). Fare attenzione quando si utilizzano tali richieste.

Come posso scoprire il numero di modelli di PC prodotti da un determinato fornitore? Come determinare il prezzo medio per computer che hanno lo stesso specifiche? Queste e molte altre domande relative ad alcuni informazioni statistiche, puoi ottenere risposte con funzioni riassuntive (aggregate)... La norma prevede le seguenti funzioni aggregate:

Tutte queste funzioni restituiscono un singolo valore. In questo caso, le funzioni CONTE, MIN e MAX sono applicabili a qualsiasi tipo di dati, mentre SOMMA e AVG vengono utilizzati solo per i campi numerici. Differenza tra funzione CONTARE (*) e CONTARE (<имя поля>) è che il secondo non tiene conto dei valori NULL durante il calcolo.

Esempio. Trova il prezzo minimo e massimo per i personal computer:

Esempio. Trova il numero di computer disponibili dal produttore A:

Esempio. Se siamo interessati alla quantità diversi modelli prodotto dal produttore A, la query può essere formulata come segue (sfruttando il fatto che nella tabella Prodotto ogni modello viene registrato una volta):

Esempio. Trova il numero di diversi modelli disponibili dal produttore A. La query è simile alla precedente, in cui era necessario determinare numero totale modelli prodotti dal produttore A. Qui è necessario anche trovare il numero dei diversi modelli nella tabella PC (cioè disponibili sul mercato).

Per garantire che vengano utilizzati solo valori univoci quando si ottengono indicatori statistici, quando argomento delle funzioni aggregate può essere utilizzato Parametro DISTINTO... Un altro parametro ALLè l'impostazione predefinita e presuppone che vengano conteggiati tutti i valori restituiti nella colonna. Operatore,

Se abbiamo bisogno di ottenere il numero di modelli di PC prodotti ogni produttore, sarà necessario utilizzare Clausola GROUP BY segue sintatticamente clausole WHERE.

Clausola GROUP BY

Clausola GROUP BY utilizzato per definire gruppi di linee di output a cui può essere applicato funzioni aggregate (COUNT, MIN, MAX, AVG e SUM)... Se questa clausola è mancante e vengono utilizzate funzioni aggregate, allora tutte le colonne con i nomi menzionati in SELEZIONARE dovrebbe essere incluso in funzioni aggregate e queste funzioni verranno applicate all'intero set di righe che soddisfano il predicato della query. Altrimenti, tutte le colonne dell'elenco SELECT, non incluso nelle funzioni aggregate, deve essere specificato nella clausola GROUP BY... Di conseguenza, tutte le righe di output della query sono suddivise in gruppi caratterizzati dalle stesse combinazioni di valori in queste colonne. Successivamente, le funzioni aggregate verranno applicate a ciascun gruppo. Nota che per GROUP BY tutti i valori NULL sono trattati come uguali, ad es. quando raggruppate da un campo contenente valori NULL, tutte queste righe rientreranno in un gruppo.
Se se c'è una clausola GROUP BY, nella clausola SELECT nessuna funzione aggregata, la query restituirà semplicemente una riga da ciascun gruppo. Questa funzionalità, insieme alla parola chiave DISTINCT, può essere utilizzata per eliminare le righe duplicate nel set di risultati.
Diamo un'occhiata a un semplice esempio:

SELEZIONA modello, COUNT (modello) AS Qty_model, AVG (price) AS Avg_price DA PC Modello GROUP BY;

In questa richiesta, per ogni modello di PC, viene determinato il loro numero e costo medio... Tutte le linee con gli stessi valori modello (numero di modello) formano un gruppo e l'output SELECT calcola il numero di valori e i valori di prezzo medio per ciascun gruppo. La query risulterà nella seguente tabella:

modello	Qtà_modello	Prezzo_medio
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

Se il SELECT avesse una colonna con una data, sarebbe possibile calcolare questi indicatori per ogni data specifica. Per fare ciò, è necessario aggiungere una data come colonna di raggruppamento, quindi le funzioni aggregate verranno calcolate per ogni combinazione di valori (model-date).

Ci sono diversi specifici regole per lo svolgimento di funzioni aggregate:

• Se come risultato della query nessuna linea ricevuta(o più di una riga per un dato gruppo), i dati iniziali per il calcolo di una qualsiasi delle funzioni aggregate sono assenti. In questo caso, il risultato dell'esecuzione delle funzioni COUNT sarà zero e il risultato di tutte le altre funzioni sarà NULL.
• Discussione funzione aggregata non può contenere di per sé funzioni aggregate(funzione da funzione). Quelli. in una query, non puoi, ad esempio, ottenere i valori medi massimi.
• Il risultato dell'esecuzione della funzione COUNT è numero intero(NUMERO INTERO). Altre funzioni aggregate ereditano i tipi di dati dei valori in elaborazione.
• Se, durante l'esecuzione della funzione SOMMA, si è ottenuto un risultato che supera il valore massimo del tipo di dati utilizzato, errore.

Quindi, se la richiesta non contiene Clausole GROUP BY, poi funzioni aggregate incluso in clausola SELECT, vengono eseguiti su tutte le righe di query risultanti. Se la richiesta contiene Clausola GROUP BY, ogni set di righe che ha gli stessi valori di colonna o gruppo di colonne specificati in Clausola GROUP BY, costituisce un gruppo, e funzioni aggregate vengono eseguite separatamente per ogni gruppo.

clausola HAVING

Se Dove la clausola definisce un predicato per filtrare le righe, quindi AVERE un'offerta applicato dopo il raggruppamento definire un predicato simile filtrando i gruppi in base ai valori funzioni aggregate... Questa clausola è necessaria per testare i valori che si ottengono usando funzione aggregata non da righe separate dell'origine record definita in clausola FROM, e da gruppi di tali linee... Pertanto, tale controllo non può essere contenuto in Dove la clausola.

Le seguenti sottosezioni descrivono altri suggerimenti Istruzione SELECT che possono essere utilizzati nelle query, nonché funzioni aggregate e set di operatori. Lascia che te lo ricordi questo momento abbiamo trattato l'uso della clausola WHERE e in questo articolo esamineremo le clausole GROUP BY, ORDER BY e HAVING e forniremo alcuni esempi di come queste clausole possono essere utilizzate insieme alle funzioni aggregate supportate in Transact -SQL.

Clausola GROUP BY

Offerta RAGGRUPPARE PER raggruppa il set di righe selezionato per ottenere un insieme di righe di riepilogo in base ai valori di una o più colonne o espressioni. Un semplice caso d'uso per la clausola GROUP BY è mostrato nell'esempio seguente:

UTILIZZARE SampleDb; SELEZIONA Lavoro FROM Lavori_On GRUPPO PER Lavoro;

In questo esempio, le posizioni dei dipendenti vengono campionate e raggruppate.

Nell'esempio sopra, la clausola GROUP BY crea un gruppo separato per tutti valori possibili(Compreso valore nullo) della colonna Lavoro.

L'utilizzo di colonne nella clausola GROUP BY deve soddisfare determinate condizioni. In particolare, ogni colonna dell'elenco di selezione della query deve comparire anche nella clausola GROUP BY. Questo requisito non si applica alle costanti e alle colonne che fanno parte di una funzione di aggregazione. (Le funzioni aggregate sono discusse nella prossima sottosezione.) Questo ha senso perché solo le colonne nella clausola GROUP BY hanno un valore garantito per gruppo.

Una tabella può essere raggruppata in base a qualsiasi combinazione delle sue colonne. L'esempio seguente mostra il raggruppamento di righe nella tabella Works_on da due colonne:

UTILIZZARE SampleDb; SELEZIONA NumeroProgetto, Lavoro FROM Lavori_On GROUP BY NumeroProgetto, Lavoro;

Il risultato di questa query:

In base ai risultati della query, puoi vedere che ci sono nove gruppi con diverse combinazioni di numero di progetto e titolo. La sequenza dei nomi delle colonne nella clausola GROUP BY non deve essere la stessa dell'elenco delle colonne SELECT.

Funzioni aggregate

Le funzioni aggregate vengono utilizzate per ottenere valori di somma. Tutte le funzioni aggregate possono essere suddivise nelle seguenti categorie:

funzioni aggregate ordinarie;

funzioni di aggregazione statistica;

funzioni aggregate definite dall'utente;

funzioni di aggregazione analitica.

Qui esamineremo i primi tre tipi di funzioni aggregate.

Funzioni aggregate regolari

Transact-SQL supporta le seguenti sei funzioni aggregate: MIN, MAX, SOMMA, AVG, CONTARE, COUNT_BIG.

Tutte le funzioni aggregate eseguono calcoli su un singolo argomento, che può essere una colonna o un'espressione. (L'unica eccezione è la seconda forma di due funzioni, COUNT e COUNT_BIG, rispettivamente COUNT (*) e COUNT_BIG (*).) Qualsiasi funzione aggregata restituisce un valore costante che viene visualizzato in una colonna separata del risultato.

Le funzioni aggregate sono specificate nell'elenco di colonne di un'istruzione SELECT, che può anche contenere una clausola GROUP BY. Se l'istruzione SELECT non include una clausola GROUP BY e l'elenco delle colonne di selezione contiene almeno una funzione di aggregazione, non deve contenere colonne semplici (diverse dalle colonne utilizzate come argomenti per la funzione di aggregazione). Pertanto, il codice nell'esempio seguente non è corretto:

UTILIZZARE SampleDb; SELECT Cognome, MIN (Id) FROM Impiegato;

Qui, la colonna Cognome della tabella Impiegato non dovrebbe essere nell'elenco di colonne di selezione perché non è un argomento per la funzione di aggregazione. D'altra parte, un elenco selezionato di colonne può contenere nomi di colonne che non sono argomenti per la funzione di aggregazione se tali colonne vengono utilizzate come argomenti per la clausola GROUP BY.

L'argomento della funzione aggregata può essere preceduto da uno dei due possibili parole chiave:

TUTTI

Indica che i calcoli vengono eseguiti su tutti i valori nella colonna. Questa è l'impostazione predefinita.

DISTINTO

Indica che solo valori unici colonna.

Funzioni aggregate MIN e MAX

Le funzioni aggregate MIN e MAX calcolano rispettivamente i valori più piccolo e più grande in una colonna. Se la query contiene una clausola WHERE, le funzioni MIN e MAX restituiscono i valori di riga più piccoli e più grandi che corrispondono alle condizioni specificate. L'esempio seguente mostra l'uso della funzione di aggregazione MIN:

UTILIZZARE SampleDb; - Restituisce 2581 SELECT MIN (Id) AS "Valore Id minimo" FROM Impiegato;

Il risultato restituito nell'esempio precedente non è molto informativo. Ad esempio, il cognome del dipendente che possiede questo numero è sconosciuto. Ma non puoi ottenere questo cognome nel solito modo, perché, come accennato in precedenza, non puoi specificare esplicitamente la colonna LastName. Per ottenere il cognome di questo dipendente insieme al numero di personale più basso del dipendente, viene utilizzata una sottoquery. L'esempio seguente mostra l'uso di tale sottoquery, dove la sottoquery contiene l'istruzione SELECT dell'esempio precedente:

Il risultato dell'esecuzione della query:

L'uso della funzione di aggregazione MAX è mostrato nell'esempio seguente:

Le funzioni MIN e MAX possono anche accettare stringhe e date come argomenti. Nel caso di un argomento stringa, i valori vengono confrontati utilizzando l'ordinamento effettivo. Per tutti gli argomenti di dati temporanei di tipo data, il valore della colonna più piccolo sarà il più appuntamento anticipato, e il più recente è il più grande.

La parola chiave DISTINCT può essere utilizzata con le funzioni MIN e MAX. Tutti i valori NULL vengono rimossi dalle colonne degli argomenti prima di utilizzare le funzioni aggregate MIN e MAX.

SOMMA aggregata funzione

Aggregato Funzione SOMMA calcola la somma totale dei valori della colonna. L'argomento di questa funzione di aggregazione deve essere sempre di tipo numerico. L'uso della funzione di aggregazione SUM è mostrato nell'esempio seguente:

UTILIZZARE SampleDb; SELEZIONA SOMMA (Budget) "Budget totale" DA Progetto;

Questo esempio calcola i budget totali per tutti i progetti. Il risultato dell'esecuzione della query:

In questo esempio, la funzione di aggregazione raggruppa tutti i valori del budget del progetto e ne determina il totale. Per questo motivo, la query contiene una funzione di raggruppamento implicita (come tutte le query simili). La funzione di raggruppamento implicita dell'esempio precedente può essere specificata in modo esplicito, come mostrato nell'esempio seguente:

UTILIZZARE SampleDb; SELECT SUM (Budget) "Budget totale" FROM Progetto GRUPPO PER ();

L'utilizzo del parametro DISTINCT rimuove eventuali valori duplicati nella colonna prima di applicare la funzione SUM. Allo stesso modo, rimuovere tutti i valori NULL prima di applicare questa funzione di aggregazione.

Funzione di aggregazione AVG

Aggregato Funzione AVG restituisce la media aritmetica di tutti i valori in una colonna. L'argomento di questa funzione di aggregazione deve essere sempre di tipo numerico. Tutti i valori NULL vengono rimossi dall'argomento prima che venga applicata la funzione AVG.

L'uso della funzione di aggregazione AVG è mostrato nell'esempio seguente:

UTILIZZARE SampleDb; - Restituisce 133833 SELECT AVG (Budget) "Budget medio per un progetto" FROM Project;

Qui è dove viene calcolata la media valore aritmetico budget per tutti i budget.

Funzioni aggregate COUNT e COUNT_BIG

Aggregato Funzione COUNT ha due diverse forme:

COUNT (col_name) COUNT (*)

La prima forma della funzione conta il numero di valori nella colonna col_name. Se la query utilizza la parola chiave DISTINCT, tutti i valori di colonna duplicati vengono rimossi prima che venga utilizzata la funzione COUNT. Questa forma della funzione COUNT non tiene conto dei valori NULL durante il conteggio del numero di valori di colonna.

L'uso della prima forma della funzione di aggregazione COUNT è mostrato nell'esempio seguente:

UTILIZZARE SampleDb; SELECT ProjectNumber, COUNT (DISTINCT Job) "Lavori in Project" FROM Works_on GROUP BY ProjectNumber;

Qui è dove viene conteggiato il numero di posizioni diverse per ogni progetto. Il risultato di questa query:

Come puoi vedere dalla query nell'esempio, i valori NULL non sono stati presi in considerazione dalla funzione COUNT. (La somma di tutti i valori nella colonna del lavoro è 7, non 11 come dovrebbe essere.)

La seconda forma della funzione COUNT, ad es. la funzione COUNT (*) conta il numero di righe in una tabella. E se l'istruzione SELECT della query con la funzione COUNT (*) contiene una clausola WHERE con una condizione, la funzione restituisce il numero di righe che soddisfano la condizione specificata. A differenza della prima versione della funzione COUNT, la seconda forma non ignora i valori NULL perché questa funzione opera su stringhe, non colonne. L'esempio seguente mostra l'uso della funzione COUNT (*):

UTILIZZARE SampleDb; SELECT Job AS "Tipo di lavoro", COUNT (*) "Lavoratori necessari" FROM Works_on GROUP BY Job;

Qui è dove viene conteggiato il numero di post in tutti i progetti. Il risultato dell'esecuzione della query:

COUNT_BIG funzione stessa funzione di COUNT. L'unica differenza tra i due è il tipo di risultato che restituiscono: COUNT_BIG restituisce sempre valori BIGINT, mentre COUNT restituisce valori di dati INTEGER.

Funzioni statistiche aggregate

Le seguenti funzioni costituiscono il gruppo delle funzioni aggregate statistiche:

VAR

Calcola la varianza statistica di tutti i valori in una colonna o espressione.

VARP

Calcola la varianza statistica della popolazione di tutti i valori in una colonna o espressione.

STDEV

Calcola la deviazione standard (che viene calcolata come Radice quadrata dalla varianza corrispondente) di tutti i valori della colonna o dell'espressione.

STDEVP

Calcola la deviazione standard della raccolta di tutti i valori in una colonna o espressione.

Funzioni aggregate definite dall'utente

Il Motore di database supporta anche l'implementazione di funzioni definite dall'utente. Questa capacità consente agli utenti di integrare le funzioni aggregate del sistema con funzioni che possono implementare e installare autonomamente. Queste funzioni rappresentano una classe speciale di funzioni definite dall'utente e vengono discusse in dettaglio in seguito.

clausola HAVING

In una frase AVENDO definisce una condizione che viene applicata a un gruppo di righe. Pertanto, questa clausola ha lo stesso significato per i gruppi di righe della clausola WHERE per il contenuto della tabella corrispondente. Sintassi AVERE offerte prossimo:

AVERE condizione

Qui il parametro condition rappresenta una condizione e contiene funzioni aggregate o costanti.

L'uso della clausola HAVING in combinazione con la funzione di aggregazione COUNT (*) è mostrato nell'esempio seguente:

UTILIZZARE SampleDb; - Restituisce "p3" SELECT ProjectNumber FROM Works_on GROUP BY ProjectNumber HAVING COUNT (*)

In questo esempio, il sistema utilizza la clausola GROUP BY per raggruppare tutte le righe in base ai valori nella colonna ProjectNumber. Viene quindi contato il numero di righe in ciascun gruppo e vengono selezionati i gruppi contenenti meno di quattro righe (tre o meno).

La clausola HAVING può essere utilizzata anche senza funzioni aggregate, come mostrato nell'esempio seguente:

UTILIZZARE SampleDb; - Restituirà "Consulente" SELECT Job FROM Works_on GROUP BY Job HAVING Job LIKE "К%";

Questo esempio raggruppa le righe della tabella Works_on per titolo di lavoro ed elimina i lavori che non iniziano con la lettera "K".

La clausola HAVING può essere utilizzata anche senza la clausola GROUP BY, sebbene questa non sia una pratica comune. In questo caso, tutte le righe della tabella vengono restituite nello stesso gruppo.

Clausola ORDER BY

Offerta ORDINATO DA determina l'ordinamento delle righe nel set di risultati restituito dalla query. Questa clausola ha la seguente sintassi:

L'ordinamento è specificato nel parametro col_name. Col_number è un indicatore di ordinamento alternativo che identifica le colonne nell'ordine in cui vengono visualizzate nell'elenco di selezione dell'istruzione SELECT (1 è la prima colonna, 2 è la seconda colonna e così via). Parametro ASC definisce un ordine crescente, e Parametro DESC- a valle. L'impostazione predefinita è ASC.

I nomi delle colonne nella clausola ORDER BY non devono necessariamente essere nell'elenco delle colonne selezionate. Ma questo non si applica a query come SELECT DISTINCT, poiché in tali interrogazioni, i nomi delle colonne specificati nella clausola ORDER BY devono comparire anche nell'elenco delle colonne selezionate. Inoltre, questa clausola non può contenere nomi di colonne di tabelle non specificate nella clausola FROM.

Come puoi vedere dalla sintassi della clausola ORDER BY, l'ordinamento del set di risultati può essere eseguito su più colonne. Questo ordinamento è mostrato nell'esempio seguente:

Questo esempio seleziona i numeri di reparto, i cognomi e i nomi dei dipendenti per i dipendenti il ​​cui numero di personale è inferiore a 20.000, nonché ordinati per cognome e nome. Il risultato di questa query:

Le colonne nella clausola ORDER BY possono essere specificate non con i loro nomi, ma in ordine nell'elenco di selezione. Di conseguenza, la frase nell'esempio precedente può essere riscritta come segue:

Tale modo alternativo la specificazione delle colonne in base alla loro posizione anziché ai nomi viene utilizzata se il criterio di ordinamento contiene una funzione aggregata. (Un altro modo consiste nell'utilizzare i nomi delle colonne, che vengono poi visualizzati nella clausola ORDER BY.) Tuttavia, nella clausola ORDER BY, si consiglia di specificare le colonne in base ai loro nomi anziché ai numeri, per semplificare l'aggiornamento della query se è necessario aggiungere o rimuovere colonne nell'elenco di selezione. La specifica delle colonne nella clausola ORDER BY in base ai loro numeri è mostrata nell'esempio seguente:

UTILIZZARE SampleDb; SELECT ProjectNumber, COUNT (*) "Numero di dipendenti" FROM Works_on GRUPPO PER ProjectNumber ORDER BY 2 DESC;

Qui, per ogni progetto, vengono selezionati il ​​numero del progetto e il numero di dipendenti che vi partecipano, ordinando il risultato in ordine decrescente per il numero di dipendenti.

Transact-SQL inserisce i valori NULL all'inizio dell'elenco se ordinati in ordine crescente e i valori NULL alla fine dell'elenco se ordinati in ordine decrescente.

Utilizzo della clausola ORDER BY per impaginare i risultati

La visualizzazione dei risultati della query nella pagina corrente può essere implementata in applicazione personalizzata o istruire il server del database a farlo. Nel primo caso, tutte le righe del database vengono inviate all'applicazione, il cui compito è selezionare le righe richieste e visualizzarle. Nel secondo caso, solo le righe richieste per la pagina corrente vengono recuperate e visualizzate dal lato server. Come ci si potrebbe aspettare, la creazione di pagine lato server di solito fornisce prestazioni migliori da al cliente vengono inviate solo le righe necessarie per la visualizzazione.

Per supportare la creazione di pagine lato server, SQL Server 2012 introduce due nuove clausole SELECT: OFFSET e FETCH. L'applicazione di queste due frasi è dimostrata nell'esempio seguente. Qui, dal database AdventureWorks2012 (che puoi trovare nella fonte), estrae l'ID aziendale, il titolo di lavoro e la data di nascita di tutte le dipendenti di sesso femminile, ordinati per titolo di lavoro in ordine crescente. Il set di righe risultante viene suddiviso in pagine di 10 righe e viene visualizzata una terza pagina:

In una frase COMPENSARE specifica il numero di righe di risultato da saltare nel risultato visualizzato. Questo importo viene calcolato dopo l'ordinamento delle righe con la clausola ORDER BY. In una frase PRENDI SUCCESSIVO il numero di soddisfazioni Dove la clausola e le righe ordinate da restituire. Il parametro di questa clausola può essere una costante, un'espressione o il risultato di un'altra query. La clausola FETCH NEXT è simile alla clausola PRENDI PRIMA.

L'obiettivo principale durante la creazione di pagine sul lato server è essere in grado di implementare moduli di pagina comuni utilizzando le variabili. Questo compito può essere realizzato con Pacchetto SQL Server.

Dichiarazione SELECT e proprietà IDENTITY

IDENTITÀ proprietà consente di determinare i valori per una colonna specifica della tabella sotto forma di contatore incrementale automatico. Le colonne di un tipo di dati numerici come TINYINT, SMALLINT, INT e BIGINT possono avere questa proprietà. Per tale colonna della tabella, Motore di database genera automaticamente valori sequenziali a partire dal valore iniziale specificato. Pertanto, la proprietà IDENTITY può essere utilizzata per generare valori numerici a una cifra per la colonna selezionata.

Una tabella può contenere solo una colonna con la proprietà IDENTITY. Il proprietario della tabella ha la possibilità di specificare il valore iniziale e l'incremento, come mostrato nell'esempio seguente:

UTILIZZARE SampleDb; CREATE TABLE Prodotto (Id INT IDENTITY (10000, 1) NOT NULL, Name NVARCHAR (30) NOT NULL, Price MONEY) INSERT INTO Product (Name, Price) VALUES ("Product1", 10), ("Product2", 15) , ("Prodotto3", 8), ("Prodotto4", 15), ("Prodotto5", 40); - Restituirà 10004 SELECT IDENTITYCOL FROM Product WHERE Name = "Product5"; - Analogo all'istruzione precedente SELECT $ identity FROM Product WHERE Name = "Product5";

Questo esempio crea prima una tabella Product che contiene una colonna Id con una proprietà IDENTITY. I valori nella colonna Id vengono creati automaticamente dal sistema, partendo da 10.000 e aumentando di un passo per ogni valore successivo: 10.000, 10.001, 10.002, ecc.

Diverse funzioni e variabili di sistema sono associate alla proprietà IDENTITY. Ad esempio, il codice di esempio usa variabile di sistema $ identità... Come puoi vedere dai risultati dell'esecuzione di questo codice, questa variabile fa automaticamente riferimento alla proprietà IDENTITY. Puoi anche usare invece funzione di sistema IDENTITÀ'.

Il valore iniziale e l'incremento della colonna con la proprietà IDENTITY possono essere trovati usando le funzioni IDENT_SEED e IDENT_INCR rispettivamente. Queste funzioni vengono applicate come segue:

UTILIZZARE SampleDb; SELECT IDENT_SEED ("Prodotto"), IDENT_INCR ("Prodotto")

Come accennato, i valori di IDENTITÀ vengono impostati automaticamente dal sistema. Ma l'utente può specificare esplicitamente i propri valori per determinate stringhe assegnando al parametro IDENTITY_INSERT il valore ON prima di inserire il valore esplicito:

IMPOSTA IDENTITÀ INSERISCI nome tabella ON

Poiché il parametro IDENTITY_INSERT può essere utilizzato per impostare qualsiasi valore, inclusi i valori duplicati, su una colonna con la proprietà IDENTITY, la proprietà IDENTITY in genere non impone l'unicità dei valori di colonna. Pertanto, i vincoli UNIQUE o PRIMARY KEY devono essere applicati per imporre l'unicità dei valori di colonna.

Quando si inseriscono valori in una tabella dopo aver impostato IDENTITY_INSERT su on, il sistema crea il valore successivo nella colonna IDENTITY, incrementando il valore corrente più alto in quella colonna.

Dichiarazione CREATE SEQUENCE

L'utilizzo della proprietà IDENTITY presenta diversi inconvenienti significativi, i più significativi dei quali sono:

la proprietà è limitata alla tabella specificata;

il nuovo valore della colonna non può essere ottenuto in altro modo se non applicandolo;

la proprietà IDENTITY può essere specificata solo durante la creazione di una colonna.

Per questi motivi, SQL Server 2012 introduce sequenze che hanno la stessa semantica della proprietà IDENTITY, ma senza gli svantaggi elencati in precedenza. In questo contesto, una sequenza si riferisce alla funzionalità di un database che consente di specificare i valori del contatore per diversi oggetti del database, come colonne e variabili.

Le sequenze vengono create utilizzando l'istruzione CREA SEQUENZA... L'istruzione CREATE SEQUENCE è definita in Standard SQL ed è supportato da altri sistemi di database relazionali come IBM DB2 e Oracle.

L'esempio seguente mostra come creare una sequenza in SQL Server:

UTILIZZARE SampleDb; CREATE SEQUENCE dbo.Sequence1 AS INT INIZIA CON 1 INCREMENTO DI 5 MINVALUE 1 MAXVALUE 256 CYCLE;

Nell'esempio sopra, i valori di Sequence1 sono generati automaticamente dal sistema, partendo da un valore di 1 e con incrementi di 5 per ogni valore successivo. Così, in la clausola START viene indicato il valore iniziale, e in INCREMENTO offerta- fare un passo. (Il passo può essere positivo o negativo.)

Nelle prossime due frasi opzionali VALORE MINIMO e VALOREMAX specifica il valore minimo e massimo dell'oggetto sequenza. (Nota che MINVALUE deve essere minore o uguale a valore iniziale e il valore MAXVALUE non può essere maggiore del limite superiore del tipo di dati specificato per la sequenza.) Nella clausola CICLO indica che la sequenza viene ripetuta dall'inizio quando viene superato il valore massimo (o minimo per una sequenza con passo negativo). Per impostazione predefinita, questa clausola è impostata su NO CYCLE, il che significa che il superamento del valore di sequenza massimo o minimo genera un'eccezione.

La caratteristica principale delle sequenze è la loro indipendenza dalle tabelle, ad es. possono essere utilizzati con qualsiasi oggetto di database come colonne di tabelle o variabili. (Questa proprietà ha un effetto positivo sull'archiviazione e, quindi, sulle prestazioni. Non è necessario archiviare una sequenza specifica, viene archiviato solo il suo ultimo valore.)

I nuovi valori di sequenza vengono creati con PROSSIMO VALORE PER le espressioni, la cui applicazione è mostrata nell'esempio seguente:

UTILIZZARE SampleDb; - Restituisce 1 SELECT NEXT VALUE FOR dbo.sequence1; - Restituisce 6 (passo successivo) SELECT NEXT VALUE FOR dbo.sequence1;

È possibile utilizzare l'espressione NEXT VALUE FOR per assegnare il risultato di una sequenza a una variabile oa una cella di colonna. L'esempio seguente illustra l'uso di questa espressione per assegnare risultati a una colonna:

UTILIZZARE SampleDb; CREATE TABLE Prodotto (Id INT NOT NULL, Nome NVARCHAR (30) NOT NULL, Price MONEY) INSERT IN Product VALUES (NEXT VALUE FOR dbo.sequence1, "Product1", 10); INSERT INTO Product VALUES (NEXT VALUE FOR dbo.sequence1, "Product2", 15); -...

L'esempio sopra crea prima una tabella chiamata Product con quattro colonne. Successivamente, due istruzioni INSERT inseriscono due righe in questa tabella. Le prime due celle della prima colonna saranno 11 e 16.

L'esempio seguente mostra l'uso della vista del catalogo sys.sequences per visualizzare il valore corrente di una sequenza senza utilizzarlo:

In genere, l'espressione NEXT VALUE FOR viene utilizzata in un'istruzione INSERT per indicare al sistema di inserire i valori generati. Questa espressione può essere utilizzata anche come parte di una query su più righe utilizzando la clausola OVER.

Per modificare una proprietà di una sequenza esistente, applica Dichiarazione ALTER SEQUENCE... Uno degli usi più importanti di questa istruzione è con l'opzione RESTART WITH, che reimposta la sequenza specificata. L'esempio seguente mostra l'uso dell'istruzione ALTER SEQUENCE per reimpostare quasi tutte le proprietà di Sequence1:

UTILIZZARE SampleDb; ALTER SEQUENCE dbo.sequence1 RIAVVIA CON 100 INCREMENTA DI 50 VALORE MIN 50 VALORE MAX 200 NESSUN CICLO;

La sequenza viene cancellata usando l'istruzione SEQUENZA DI CADUTA.

Imposta operatori

Oltre agli operatori discussi in precedenza, Transact-SQL supporta altri tre operatori di insiemi: UNION, INTERSECT ed EXCEPT.

operatore UNION

operatore UNION combina i risultati di due o più query in un unico set di risultati che include tutte le righe appartenenti a tutte le query nell'unione. Di conseguenza, il risultato dell'unione delle due tabelle è una nuova tabella contenente tutte le righe in una o entrambe le tabelle originali.

La forma generale dell'operatore UNION si presenta così:

select_1 UNION select_2 (select_3]) ...

Le opzioni select_1, select_2, ... sono istruzioni SELECT che creano il join. Se viene utilizzato il parametro ALL, vengono visualizzate tutte le righe, inclusi i duplicati. Nell'istruzione UNION, il parametro ALL ha lo stesso significato di SELECT, con una differenza: per SELECT questo parametro è l'impostazione predefinita e per UNION deve essere specificato in modo esplicito.

Nella sua forma originale, SampleDb non è adatto per dimostrare l'uso dell'operatore UNION. Pertanto in questa sezione viene creata una nuova tabella EmployeeEnh che è identica tabella esistente Dipendente ma ha una colonna Città aggiuntiva. In questa colonna è indicato il luogo di residenza dei dipendenti.

La creazione della tabella EmployeeEnh ci offre l'opportunità di dimostrare l'uso della clausola. IN nell'istruzione SELECT. L'istruzione SELECT INTO esegue due operazioni. Innanzitutto, viene creata una nuova tabella con le colonne elencate nell'elenco SELECT. Quindi le righe della tabella originale vengono inserite in nuova tabella... Il nome della nuova tabella è specificato nella clausola INTO e il nome della tabella di origine è specificato nella clausola FROM.

L'esempio seguente mostra la creazione della tabella EmployeeEnh dalla tabella Employee:

UTILIZZARE SampleDb; SELEZIONA * IN ImpiegatoEnh FROM Impiegato; ALTER TABLE ImpiegatoEnh ADD Città NCHAR (40) NULL;

In questo esempio, l'istruzione SELECT INTO crea la tabella EmployeeEnh, inserisce in essa tutte le righe della tabella di origine Employee e quindi l'istruzione ALTER TABLE aggiunge la colonna City alla nuova tabella. Ma la colonna Città aggiunta non contiene alcun valore. I valori in questa colonna possono essere inseriti utilizzando l'ambiente Studio di gestione oppure con il seguente codice:

UTILIZZARE SampleDb; UPDATE EmployeeEnh SET City = "Kazan" WHERE Id = 2581; UPDATE EmployeeEnh SET City = "Mosca" WHERE Id = 9031; UPDATE EmployeeEnh SET City = "Ekaterinburg" WHERE Id = 10102; UPDATE EmployeeEnh SET City = "San Pietroburgo" WHERE Id = 18316; UPDATE EmployeeEnh SET City = "Krasnodar" WHERE Id = 25348; UPDATE EmployeeEnh SET City = "Kazan" WHERE Id = 28559; UPDATE EmployeeEnh SET City = "Perm" WHERE Id = 29346;

Siamo ora pronti a dimostrare l'uso della dichiarazione UNION. L'esempio seguente mostra una query per creare un join tra le tabelle EmployeeEnh e Department utilizzando questa istruzione:

UTILIZZARE SampleDb; SELECT City AS "Città" FROM ImpiegatoEnh UNION SELECT Location FROM Dipartimento;

Il risultato di questa query:

È possibile unire solo tabelle compatibili utilizzando l'istruzione UNION. Per tabelle compatibili intendiamo che entrambi gli elenchi di colonne nella selezione devono contenere lo stesso numero di colonne e le colonne corrispondenti devono avere tipi di dati compatibili. (In termini di compatibilità, i tipi di dati INT e SMALLINT non sono compatibili.)

Il risultato della concatenazione può essere ordinato solo utilizzando la clausola ORDER BY in ultima istruzione SELEZIONARE come mostrato nell'esempio seguente. Offerte GRUPPO BY e HAVING possono essere utilizzati con istruzioni separate SELECT, ma non nell'unione stessa.

La query in questo esempio seleziona i dipendenti che lavorano nel reparto d1 o che hanno iniziato a lavorare a un progetto prima del 1 gennaio 2008.

L'operatore UNION supporta l'opzione ALL. Questa opzione non rimuove i duplicati dal set di risultati. Un operatore OR può essere utilizzato al posto dell'operatore UNION se tutte le istruzioni SELECT unite da uno o più operatori UNION fanno riferimento alla stessa tabella. In questo caso, l'insieme di istruzioni SELECT viene sostituito da una singola istruzione SELECT con un insieme di istruzioni OR.

Operatori INTERSECT e EXCEPT

Altri due operatori per lavorare con gli insiemi, INTERSEZIONE e TRANNE, definiscono rispettivamente l'intersezione e la differenza. Sotto l'intersezione, in questo contesto, c'è un insieme di righe che appartengono a entrambe le tabelle. E la differenza tra due tabelle è definita come tutti i valori che appartengono alla prima tabella e non sono presenti nella seconda. L'esempio seguente mostra l'uso dell'operatore INTERSECT:

Transact-SQL non supporta l'utilizzo del parametro ALL con gli operatori INTERSECT o EXCEPT. L'uso dell'operatore EXCEPT è mostrato nell'esempio seguente:

Si ricorda che questi tre operatori di insieme hanno precedenza di esecuzione diversa: l'operatore INTERSECT ha la precedenza più alta, seguito da TRANNE dichiarazione e l'operatore UNION ha la precedenza più bassa. La mancata attenzione alla precedenza di esecuzione quando si utilizzano diversi operatori di insiemi diversi può portare a risultati imprevisti.

Espressioni CASE

Nel campo della programmazione di applicazioni di database, a volte è necessario modificare la presentazione dei dati. Ad esempio, le persone possono essere suddivise per classe sociale, utilizzando i valori 1, 2 e 3, che denotano rispettivamente uomini, donne e bambini. Questa tecnica di programmazione può ridurre il tempo necessario per implementare il programma. Espressione CASE Transact-SQL consente di implementare facilmente questo tipo di codifica.

A differenza della maggior parte dei linguaggi di programmazione, CASE non è un'istruzione, ma un'espressione. Pertanto, un'espressione CASE può essere utilizzata quasi ovunque Transact-SQL consente l'utilizzo di espressioni. L'espressione CASE ha due forme:

semplice espressione CASE;

espressione di ricerca CASO.

La sintassi per una semplice espressione CASE è la seguente:

Un'istruzione con una semplice espressione CASE cerca prima l'elenco di tutte le espressioni in Clausola WHEN la prima espressione che corrisponde a expression_1 e quindi esegue la corrispondente THEN clausola... Se non ci sono espressioni corrispondenti nell'elenco WHEN, allora clausola ELSE.

La sintassi per un'espressione di ricerca CASE è la seguente:

V in questo caso viene cercata la prima condizione corrispondente, quindi viene eseguita la clausola THEN corrispondente. Se nessuna delle condizioni soddisfa i requisiti, viene eseguita la clausola ELSE. L'uso dell'espressione di ricerca CASE è mostrato nell'esempio seguente:

UTILIZZARE SampleDb; SELEZIONARE NomeProgetto, CASO QUANDO Budget> 0 AND Budget 100000 AND Budget 150000 AND Budget

Il risultato di questa query:

Questo esempio pesa i budget di tutti i progetti e visualizza i pesi calcolati insieme ai nomi dei progetti corrispondenti.

L'esempio seguente mostra un altro modo per utilizzare un'espressione CASE in cui la clausola WHEN contiene sottoquery che fanno parte dell'espressione:

UTILIZZARE SampleDb; SELECT ProjectName, CASE WHEN p1.Budget (SELECT AVG (p2.Budget) FROM Project p2) THEN "sopra la media" END "Categoria budget" FROM Project p1;

Il risultato di questa query è il seguente:

Utilizzo di funzioni aggregate

SQL definisce molte funzioni integrate di varie categorie, tra le quali un posto speciale è occupato da funzioni aggregate che operano sui valori delle colonne in un insieme di righe e restituiscono un singolo valore. Gli argomenti delle funzioni aggregate possono essere sia colonne di tabella che i risultati di espressioni su di esse. Le funzioni aggregate possono esse stesse essere incluse in altre espressioni aritmetiche. La tabella seguente elenca le funzioni aggregate unari standard più comunemente utilizzate.

Il formato generale di una funzione aggregata unaria è il seguente:

nome_funzione (espressione [ALL | DISTINCT])

dove DISTINCT indica che la funzione deve considerare solo significati diversi argomento e ALL include tutti i valori, inclusi i duplicati (questa è l'impostazione predefinita). Ad esempio, la funzione AVG con la parola chiave DISTINCT per le righe di colonna con i valori 1, 1, 1 e 3 restituirà 2 e, se è presente la parola chiave ALL, restituirà 1.5.

Le funzioni aggregate vengono utilizzate nelle clausole SELECT e HAVING. Qui esamineremo il loro utilizzo nella clausola SELECT. In questo caso, l'espressione nell'argomento della funzione si applica a tutte le righe nella tabella di input della clausola SELECT. Inoltre, nella clausola SELECT, non è possibile utilizzare sia funzioni aggregate che colonne di tabella (o espressioni con esse) in assenza della clausola GROUP BY, che considereremo in sezione successiva.

La funzione COUNT ha due formati. Nel primo caso viene restituito il numero di righe nella tabella di input, nel secondo caso il numero di valori degli argomenti nella tabella di input:

• CONTARE (*)
• CONTE (espressione)

Il modo più semplice per utilizzare questa funzione è contare il numero di righe in una tabella (tutte o che soddisfano una condizione specificata). Per questo, viene utilizzata la prima sintassi.

Query: il numero di tipi di prodotti, le cui informazioni sono disponibili nel database.

SELEZIONA CONTEGGIO (*) AS "Numero di tipi di prodotti"

DA Prodotto

Nella seconda variante della sintassi per la funzione COUNT, il nome di una singola colonna può essere utilizzato come argomento. In questo caso, viene conteggiato il numero di tutti i valori in questa colonna della tabella di input o solo i valori non ripetuti (quando si utilizza la parola chiave DISTINCT).

Query: il numero di nomi distinti contenuti nella tabella Customer.

SELEZIONA CONTEGGIO (DISTINTO FNAME)

DA Cliente

L'uso del resto delle funzioni aggregate unarie è lo stesso di COUNT, tranne per il fatto che per le funzioni MIN e MAX l'uso delle parole chiave DISTINCT e ALL è privo di significato. Oltre ai campi numerici, i campi carattere possono essere utilizzati con le funzioni COUNT, MAX e MIN. Se l'argomento della funzione di aggregazione non contiene valori, la funzione COUNT restituisce 0 e tutte le altre restituiscono NULL.

SELEZIONA MAX (OrdDate)

A PARTIRE DAL

DOVE OrdDate "1.09.2010"

Incarico per lavoro indipendente: Stato in lingua Query SQL per campionare i seguenti dati:

• Il costo totale di tutti gli ordini;
• Il numero di città distinte contenute nella tabella Cliente.