GROUP BY klauzula(Izjava SELECT) omogućuje grupiranje podataka (redova) prema vrijednosti stupca ili više stupaca ili izraza. Rezultat će biti skup sažetih redaka.
Svaki stupac na popisu za odabir mora biti prisutan u klauzuli GROUP BY, jedina iznimka su konstante i stupci - operandi agregatnih funkcija.
Tablica se može grupirati bilo kojom kombinacijom svojih stupaca.
Agregatne funkcije koriste se za dobivanje jedne ukupne vrijednosti iz grupe redaka. Sve agregatne funkcije izvode izračune na jednom argumentu, koji može biti ili stupac ili izraz. Bilo koja agregatna funkcija procjenjuje se na konstantnu vrijednost koja se prikazuje u zasebnom stupcu u rezultatu.
Agregatne funkcije navedene su u popisu stupaca izraza SELECT, koji također može sadržavati klauzulu GROUP BY. Ako izraz SELECT ne sadrži klauzulu GROUP BY, a popis stupaca za odabir sadrži barem jednu agregatnu funkciju, tada ne smije sadržavati jednostavne stupce. S druge strane, odabrani popis stupaca može sadržavati nazive stupaca koji nisu argumenti agregatnoj funkciji ako se ti stupci koriste kao argumenti klauzule GROUP BY.
Ako upit sadrži klauzulu WHERE, agregatne funkcije izračunavaju vrijednost za rezultate odabira.
Funkcije agregata MIN i MAX izračunavaju se najmanja i najveća vrijednost stupca. Argumenti mogu biti brojevi, nizovi i datumi. Sve NULL vrijednosti se uklanjaju prije izračuna (tj. ne uzimaju se u obzir).
Zbirna funkcija SUM izračunava ukupni zbroj vrijednosti stupca. Samo brojevi mogu biti argumenti. Korištenje parametra DISTINCT uklanja sve duplicirane vrijednosti u stupcu prije primjene funkcije SUM. Isto tako, uklonite sve NULL vrijednosti prije primjene ove agregatne funkcije.
Agregatna funkcija AVG vraća prosjek svih vrijednosti u stupcu. Argumenti također mogu biti samo brojevi, a sve NULL vrijednosti se uklanjaju prije evaluacije.
Zbirna funkcija COUNT ima dva različita oblika:
- COUNT (col_name) - broji broj vrijednosti u stupcu col_name, NULL vrijednosti se ne broje
- COUNT (*) - broji broj redaka u tablici, NULL vrijednosti se također uzimaju u obzir
Ako upit koristi ključnu riječ DISTINCT, sve duplicirane vrijednosti stupaca se uklanjaju prije upotrebe funkcije COUNT.
COUNT_BIG funkcija ista funkcija kao COUNT. Jedina razlika između njih je u vrsti rezultata koji vraćaju: COUNT_BIG uvijek vraća BIGINT vrijednosti, dok COUNT vraća INTEGER vrijednosti podataka.
V IMATI ponudu definira uvjet koji se primjenjuje na grupu redaka. Za grupe redaka ima isto značenje kao klauzula WHERE za sadržaj odgovarajuće tablice (WHERE se primjenjuje prije grupiranja, HAVING nakon).
Lekcija će pokriti temu sql preimenovanja stupca (polja) koristeći AS funkciju riječi; također je pokrio temu agregatnih funkcija u sql. Analizirat će se konkretni primjeri zahtjeva
Možete preimenovati nazive stupaca u upitima. To čini rezultate čitljivijim.
U SQL-u je preimenovanje polja povezano s upotrebom ključna riječ AS koji se koristi za preimenovanje naziva polja u skupovima rezultata
Sintaksa:
IZABERI<имя поля>KAO<псевдоним>IZ ...
Pogledajmo primjer preimenovanja u SQL-u:
Primjer DB "Institut": Prikažite imena nastavnika i njihove plaće, za one učitelje čije su plaće ispod 15000 preimenujte polje zarplata u "Niska plaća"
✍ Rješenje:
Preimenovanje stupaca u SQL-u često je potrebno prilikom izračunavanja vrijednosti povezanih s više polja tablice. Razmotrimo primjer:
Primjer DB "Institut": Iz tablice učitelja ispišite polje imena i izračunajte iznos plaće i bonusa, imenujući polje "Plaća_ nagrada"
✍ Rješenje:
| 1 2 | SELECT naziv, (zarplata + premia) AS zarplata_premia OD učitelja; |
SELECT naziv, (zarplata + premia) AS zarplata_premia OD učitelja;
Proizlaziti:
Agregatne funkcije u SQL-u
Agregatne funkcije u sql-u koriste se za dobivanje zbroja i izračunavanje izraza:
Sve agregatne funkcije vraćaju jednu vrijednost.
Funkcije COUNT, MIN i MAX primjenjuju se na bilo koju vrstu podataka.
Funkcije SUM i AVG koriste se samo za numerička polja.
Postoji razlika između funkcija COUNT (*) i COUNT (): druga ne uzima u obzir NULL vrijednosti prilikom brojanja.
Važno: pri radu sa agregatnim funkcijama u SQL-u koristi se posebna riječ KAO
Primjer DB "Institut": Dobijte vrijednost najveće plaće među nastavnicima, prikažite rezultat kao "Max_zp"
✍ Rješenje:
| SELECT MAX (zarplata) AS max_zp FROM učitelja; |
SELECT MAX (zarplata) AS max_zp FROM učitelja;
Rezultati:
Razmotrimo složeniji primjer korištenja agregatnih funkcija u sql-u.
✍ Rješenje:
GROUP BY klauzula u SQL-u
Klauzula group by u sql-u obično se koristi zajedno sa agregatnim funkcijama.
Agregatne funkcije izvode se na svim rezultirajućim recima upita. Ako upit sadrži klauzulu GROUP BY, svaki skup redaka naveden u klauzuli GROUP BY čini grupu i agregatne funkcije se izvršavaju za svaku grupu posebno.
Razmotrimo primjer s tablicom lekcija: 
Primjer:
Važno: Dakle, kao rezultat korištenja GROUP BY, svi izlazni reci upita podijeljeni su u grupe koje karakteriziraju iste kombinacije vrijednosti u tim stupcima (tj. agregatne funkcije se izvode za svaku grupu zasebno).
Treba imati na umu da kada se grupiraju po polju koje sadrži NULL vrijednosti, svi takvi zapisi će pasti u jednu grupu.
Za različite vrste pisača odredite njihovu prosječnu cijenu i količinu (tj. zasebno za laser, inkjet i matrix). Koristite agregatne funkcije. Rezultat bi trebao izgledati ovako:
Imati SQL izjavu
Klauzula HAVING u SQL-u potrebna je za provjeru vrijednosti, koji se dobivaju pomoću agregatne funkcije nakon grupiranja(nakon korištenja GROUP BY). Takav ček ne može biti sadržan u klauzuli WHERE.
Primjer: Trgovina računala DB... Izračunajte prosječnu cijenu računala s istom brzinom procesora. Izračunajte samo za one grupe čija je prosječna cijena manja od 30.000.
- Agregatne funkcije koriste se kao nazivi polja u SELECT izrazu, s jednom iznimkom: uzimaju ime polja kao argument. S funkcijama IZNOS i PROSJEČAN mogu se koristiti samo numerička polja. S funkcijama COUNT, MAX i MIN mogu se koristiti i numerička i znakovna polja. Kada se koristi s poljima znakova MAX i MINće ih prevesti u ASCII ekvivalent i obraditi ih abecednim redom. Neki DBMS-ovi dopuštaju korištenje ugniježđenih agregata, ali to je odstupanje od ANSI standarda sa svim posljedicama koje iz toga proizlaze.
Na primjer, možete izračunati broj studenata koji su položili ispite u svakoj disciplini. Da biste to učinili, trebate izvršiti upit grupiran po polju "Disciplina" i kao rezultat prikazati naziv discipline i broj redaka u grupi za ovu disciplinu. Korištenje znaka * kao argumenta funkciji COUNT znači da se broje svi redovi u grupi.
ODABIR R1. Disciplina, COUNT (*)
GRUPA PO R1. Disciplina;
Proizlaziti:
SELECT R1 Discipline, COUNT (*)
GDJE R1. NIJE NULL rezultat
GRUPA PO R1. Disciplina;
Proizlaziti:
neće biti uključeni u skup torki prije grupiranja, pa će broj torki u skupini za disciplinu "Teorija informacija" biti 1 manji.
Sličan rezultat možete dobiti ako zahtjev napišete na sljedeći način:
ODABIR R1. Disciplina, COUNT (R1. Ocjena)
GRUPA PO R1. Disciplina;
Funkcija COUNT (NAZIV ATRIBUTA) broji broj definiranih vrijednosti u grupi, za razliku od funkcije COUNT (*), koji broji broj redova u skupini. Doista, u skupini s disciplinom "Teorija informacija" bit će 4 reda, ali samo 3 specifične vrijednosti atributa "Procjena".
Pravila za rukovanje nultim vrijednostima u agregatnim funkcijama
Ako su bilo koje vrijednosti u stupcu jednake NULL isključeni su pri izračunu rezultata funkcije.
Ako su sve vrijednosti u stupcu jednake NULL, onda Maks. min. zbroj prosj = NULL, count = 0 (nula).
Ako je tablica prazna, broj (*) = 0 .
Agregatne funkcije se također mogu koristiti bez preliminarne operacije grupiranja, u ovom slučaju se cijela relacija smatra jednom grupom i za ovu grupu može se izračunati jedna vrijednost po grupi.
Pravila za tumačenje agregatnih funkcija
Agregatne funkcije mogu se uključiti u popis izlaza, a zatim se primjenjuju na cijelu tablicu.
SELECT MAX (ocjena) od R1 dat će maksimalnu ocjenu na sjednici;
ODABIR ZBOR iz R1 dat će zbroj svih ocjena po sjednici;
ODABIR PROSJEČAN (Score) iz R1 dat će prosječnu ocjenu za cijelu sesiju.
2; Rezultat: "width =" 640" Ponovo se pozivajući na bazu podataka Session (tablice R1), pronaći ćemo broj uspješno položenih ispita:
SELECT COUNT (*) As Iznajmljeno _ ispiti
GDJE Razred 2;
Proizlaziti:
Argument agregatnih funkcija mogu biti pojedinačni stupci tablica. Da bi se izračunao, na primjer, broj različitih vrijednosti određenog stupca u grupi, potrebno je koristiti ključnu riječ DISTINCT zajedno s nazivom stupca. Izračunajmo broj različitih ocjena dobivenih za svaku disciplinu:
SELECT R1 disciplina, COUNT (DISTINCT R1 evaluacija)
GDJE R1. NIJE NULL rezultat
GRUPA PO R1. Disciplina;
Proizlaziti:
Isti rezultat se dobiva ako izuzmete eksplicitni uvjet u dijelu WHERE, u kojem slučaju će upit izgledati ovako:
ODABIR R1. Disciplina, COUNT (DISTINCT R1. Ocjena)
GRUPA PO R1. Disciplina;
Funkcija COUNT (DISTINCT R1. Evaluation) smatra samo izvjesnim razne vrijednosti.
Da bi se u ovom slučaju dobio željeni rezultat, potrebno je izvršiti preliminarnu transformaciju tipa podataka stupca "Score", dovodeći ga u stvarni tip, tada rezultat izračuna prosjeka neće biti cijeli broj. U ovom slučaju zahtjev će izgledati ovako:
2 Grupirajte po R2. Grupa, R1. Disciplina; Ovdje funkcija CAST () pretvara stupac "Score" u važeći tip podataka. "širina =" 640" Odaberite R2.Grupa, R1.Disciplina, Count (*) kao Total, AVG (cast (Rezultat kao decimalni (3,1))) kao Prosječni_bod
Od R1, R2
gdje je R1. Puno ime = R2. Ime i R1. rezultat nije nul
i R1. 2. razred
Grupirajte po R2. Grupa, R1. Disciplina;
Ovdje je funkcija ULOGE () pretvara stupac Score u važeći tip podataka.
Ne možete koristiti agregatne funkcije u klauzuli WHERE jer se uvjeti u ovom odjeljku vrednuju u smislu jednog retka, a agregatne funkcije vrednuju se u smislu grupa redaka.
Klauzula GROUP BY omogućuje vam definiranje podskupa vrijednosti u određenom polju u smislu drugog polja i primjenu agregatne funkcije na podskup. To omogućuje kombiniranje polja i agregatnih funkcija u jednu klauzulu SELECT. Agregatne funkcije mogu se koristiti i u izrazu za ispis rezultata SELECT retka i u izrazu za obradu generiranih HAVING grupa. U ovom slučaju, svaka se agregatna funkcija izračunava za svaku odabranu skupinu. Vrijednosti dobivene pri izračunu agregatnih funkcija mogu se koristiti za prikaz odgovarajućih rezultata ili za uvjet odabira grupa.
Konstruirajmo upit koji prikazuje grupe u kojima je na ispitima dobiveno više od jedne dvojke u jednoj disciplini:
jedan; Rezultat: "width =" 640" ODABIR R2. Skupina
IZ R1, R2
GDJE R1. Puno ime = R2. Ime I
R1 rezultat = 2
GRUPA PO R2. Grupa, R1. Disciplina
IMATI broj (*) 1;
Proizlaziti:
Imamo DB "Banka", koja se sastoji od jedne tablice F, koja pohranjuje relaciju F, koja sadrži podatke o računima u poslovnicama određene banke:
Pronađite ukupno stanje računa u poslovnicama. Moguće je napraviti zaseban upit za svaku od njih odabirom SUM iz tablice za svaku granu, ali operacija GROUP BY će ih sve staviti u jednu naredbu:
IZABERI Podružnica , SUM ( Ostatak )
GRUPA PO Ogranak;
GRUPA PO primjenjuje agregatne funkcije neovisno za svaku grupu identificiranu vrijednošću polja Grana. Grupa se sastoji od redaka s istom vrijednošću polja Grana i funkcije IZNOS primjenjuje se zasebno za svaku takvu grupu, tj. ukupno stanje računa se obračunava posebno za svaku podružnicu. Vrijednost polja na koje se odnosi GRUPA PO, ima, po definiciji, samo jednu vrijednost po izlaznoj grupi, kao i rezultat agregatne funkcije.
5000; Argumenti u klauzuli HAVING slijede ista pravila kao u klauzuli SELECT, koja koristi GROUP BY. Moraju imati jednu vrijednost po izlaznoj grupi. "širina =" 640" Pretpostavimo da odaberete samo one podružnice čija ukupna vrijednost stanja računa prelazi 5.000 USD, kao i ukupna stanja za odabrane poslovnice. Za prikaz podružnica s ukupnim stanjem iznad 5000 USD, morate koristiti klauzulu HAVING. Klauzula HAVING definira kriterije koji se koriste za uklanjanje određenih grupa iz izlaza, baš kao što to čini klauzula WHERE za pojedinačne retke.
Ispravna naredba bi bila sljedeća:
SELECT grana, SUM (Stanje)
GRUPA PO Podružnica
IMATI SUM ( Ostatak ) 5 000;
Argumenti u rečenici IMATI poštivati ista pravila kao u rečenici IZABERI gdje se koristi GRUPA PO... Moraju imati jednu vrijednost po izlaznoj grupi.
Sljedeća naredba bit će zabranjena:
SELECT grana, SUM (Stanje)
GRUPA PO Ogranak
OTVORENI Datum = 27.12.2004 ;
Polje Datum otvaranja ne može se koristiti u rečenici IMATI jer može imati više od jedne vrijednosti po grupi prikaza. Kako bi se izbjegla takva situacija, prijedlog IMATI treba se odnositi samo na agregate i odabrana polja GRUPA PO... Postoji ispravan način za postavljanje gornjeg upita:
SELECT grana, SUM (Stanje)
WHERE Datum otvaranja = '27./12.2004.'
GRUPA PO Ogranak;
Značenje ovog upita je sljedeće: pronađite iznos stanja za svaku podružnicu računa otvorenih 27. prosinca 2004. godine.
Kao što je ranije rečeno, HAVING može koristiti samo argumente koji imaju istu vrijednost po izlaznoj grupi. U praksi su reference na agregatne funkcije najopćenitije, ali polja odabrana s GROUP BY također su važeća. Na primjer, želimo vidjeti ukupna stanja računa podružnica u Sankt Peterburgu, Pskovu i Uryupinsku:
SELECT grana, SUM (Stanje)
IZ F, Q
GDJE F. Grana = Q. Grana
GRUPA PO Ogranak
HAVING Ogranak U ('Sankt Peterburg', 'Pskov', 'Uryupinsk');
100.000; Ako je ukupni saldo veći od 100.000 USD, tada ćemo ga vidjeti u rezultirajućem omjeru, inače ćemo dobiti prazan omjer. "širina =" 640"
Stoga, u aritmetičkim izrazima predikata uključenih u uvjet odabira klauzule HAVING, možete izravno koristiti samo specifikacije stupaca navedenih kao grupiranje stupaca u klauzuli GROUP BY. Ostatak stupaca može se specificirati samo unutar specifikacija agregatnih funkcija COUNT, SUM, AVG, MIN i MAX, koje u ovom slučaju izračunavaju neku agregatnu vrijednost za cijelu grupu redaka. Rezultat izvršavanja klauzule HAVING je grupirana tablica koja sadrži samo one grupe redaka za koje je rezultat procjene uvjeta odabira u dijelu HAVING TRUE. Konkretno, ako je klauzula HAVING prisutna u upitu koji ne sadrži GROUP BY, tada će rezultat njegovog izvršenja biti ili prazna tablica, ili rezultat prethodnih dijelova izraza tablice, koji se smatra jednom grupom bez grupiranje stupaca. Pogledajmo primjer. Recimo da želimo prikazati ukupan iznos stanja za sve grane, ali samo ako je veći od 100 000 USD. U ovom slučaju, naš upit neće sadržavati operacije grupiranja, već će sadržavati odjeljak HAVING i izgledat će ovako:
ODABIR ZBOR ( Ostatak )
IMATI SUM ( Ostatak ) 100 000;
Ako je ukupni saldo veći od 100.000 USD, tada ćemo ga vidjeti u rezultirajućem omjeru, inače ćemo dobiti prazan omjer.
Sljedeći pododjeljci opisuju druge SELECT klauzule koje se mogu koristiti u upitima, kao i agregatne funkcije i skupove izraza. Dopustite mi da vas podsjetim da smo do sada pokrili upotrebu klauzule WHERE, a u ovom ćemo članku pogledati klauzule GROUP BY, ORDER BY i HAVING te dati neke primjere korištenja ovih klauzula u kombinaciji sa agregatnim funkcijama koji su podržani u Transact-SQL-u.
GROUP BY klauzula
Rečenica GRUPA PO grupira odabrani skup redaka da dobije skup sažetih redaka na temelju vrijednosti jednog ili više stupaca ili izraza. Jednostavan slučaj upotrebe klauzule GROUP BY prikazan je u primjeru u nastavku:
KORISTITE SampleDb; SELECT Job FROM Works_On GROUP BY Job;
U ovom primjeru, pozicije zaposlenika su uzorkovane i grupirane.
U gornjem primjeru, klauzula GROUP BY stvara zasebnu grupu za sve moguće vrijednosti (uključujući NULL) za stupac Zadatak.
Korištenje stupaca u klauzuli GROUP BY mora ispunjavati određene uvjete. Konkretno, svaki stupac na popisu za odabir upita također se mora pojaviti u klauzuli GROUP BY. Ovaj se zahtjev ne odnosi na konstante i stupce koji su dio agregatne funkcije. (O agregatnim funkcijama raspravlja se u sljedećem pododjeljku.) Ovo ima smisla jer samo stupci u klauzuli GROUP BY imaju zajamčenu jednu vrijednost po grupi.
Tablica se može grupirati bilo kojom kombinacijom svojih stupaca. Primjer u nastavku pokazuje grupiranje redaka u tablici Works_on po dva stupca:
KORISTITE SampleDb; SELECT ProjectNumber, Job FROM Works_On GRUPA PO Broj projekta, posao;
Rezultat ovog upita:
Na temelju rezultata upita možete vidjeti da postoji devet grupa s različitim kombinacijama broja projekta i naslova. Slijed naziva stupaca u klauzuli GROUP BY ne mora biti isti kao u popisu stupaca SELECT.
Agregatne funkcije
Agregatne funkcije se koriste za dobivanje sažetaka. Sve agregatne funkcije mogu se podijeliti u sljedeće kategorije:
obične agregatne funkcije;
statističke agregatne funkcije;
korisnički definirane agregatne funkcije;
analitičke agregatne funkcije.
Ovdje ćemo pogledati prve tri vrste agregatnih funkcija.
Redovne agregatne funkcije
Transact-SQL podržava sljedećih šest agregatnih funkcija: MIN, MAX, IZNOS, PROSJEČAN, RAČUNATI, COUNT_BIG.
Sve agregatne funkcije izvode izračune na jednom argumentu, koji može biti ili stupac ili izraz. (Jedina iznimka je drugi oblik dviju funkcija, COUNT i COUNT_BIG, odnosno COUNT (*) i COUNT_BIG (*).) Svaka agregatna funkcija daje konstantnu vrijednost koja se pojavljuje u zasebnom stupcu rezultata.
Agregatne funkcije navedene su u popisu stupaca izraza SELECT, koji također može sadržavati klauzulu GROUP BY. Ako izraz SELECT ne uključuje klauzulu GROUP BY, a popis stupaca za odabir sadrži barem jednu agregatnu funkciju, tada ne smije sadržavati jednostavne stupce (osim stupaca koji se koriste kao argumenti agregatnoj funkciji). Stoga je kod u primjeru u nastavku netočan:
KORISTITE SampleDb; SELECT Prezime, MIN (Id) FROM Employee;
Ovdje stupac Prezime u tablici Employee ne bi trebao biti na popisu stupaca za odabir jer nije argument agregatnoj funkciji. S druge strane, odabrani popis stupaca može sadržavati nazive stupaca koji nisu argumenti agregatnoj funkciji ako se ti stupci koriste kao argumenti klauzule GROUP BY.
Argumentu agregatne funkcije može prethoditi jedna od dvije moguće ključne riječi:
SVIOznačava da se izračuni provode za sve vrijednosti u stupcu. Ovo je zadana postavka.
RAZLIČITOdređuje da se za izračune koriste samo jedinstvene vrijednosti stupaca.
Funkcije agregata MIN i MAX
Funkcije agregata MIN i MAX izračunavaju najmanju i najveću vrijednost u stupcu, respektivno. Ako upit sadrži klauzulu WHERE, funkcije MIN i MAX vraćaju najmanju i najveću vrijednost retka koje odgovaraju navedenim uvjetima. Primjer u nastavku pokazuje upotrebu agregatne funkcije MIN:
KORISTITE SampleDb; - Vraća 2581 SELECT MIN (Id) AS "Minimalna vrijednost ID-a" FROM Employee;
Rezultat vraćen u gornjem primjeru nije baš informativan. Primjerice, prezime zaposlenika koji posjeduje ovaj broj nije poznato. Ali ovo prezime ne možete dobiti na uobičajen način, jer, kao što je ranije spomenuto, ne možete eksplicitno navesti stupac Prezime. Za dobivanje prezimena ovog djelatnika zajedno s najnižim kadrovskim brojem djelatnika koristi se podupit. Primjer u nastavku pokazuje upotrebu takvog potupita, gdje potupit sadrži naredbu SELECT iz prethodnog primjera:
Rezultat izvršavanja upita:
Korištenje agregatne funkcije MAX prikazano je u primjeru u nastavku:
Funkcije MIN i MAX također mogu prihvatiti nizove i datume kao argumente. U slučaju argumenta niza, vrijednosti se uspoređuju koristeći stvarni redoslijed sortiranja. Za sve argumente privremenih podataka tipa datuma, najniža vrijednost stupca je najraniji datum, a najviša vrijednost je najnoviji.
Ključna riječ DISTINCT može se koristiti s funkcijama MIN i MAX. Sve NULL vrijednosti uklanjaju se iz stupaca argumenata prije upotrebe agregatnih funkcija MIN i MAX.
Zbirna funkcija SUM
Agregat Funkcija SUM izračunava ukupni zbroj vrijednosti stupca. Argument ove agregatne funkcije uvijek mora biti numeričkog tipa podataka. Upotreba agregatne funkcije SUM prikazana je u primjeru u nastavku:
KORISTITE SampleDb; SELECT SUM (Budget) "Ukupni proračun" IZ projekta;
Ovaj primjer izračunava ukupne proračune za sve projekte. Rezultat izvršavanja upita:
U ovom primjeru, agregatna funkcija grupira sve vrijednosti proračuna projekta i određuje njihov ukupni iznos. Iz tog razloga, upit sadrži implicitnu funkciju grupiranja (kao i svi slični upiti). Implicitna funkcija grupiranja iz gornjeg primjera može se eksplicitno specificirati, kao što je prikazano u primjeru u nastavku:
KORISTITE SampleDb; SELECT SUM (Proračun) "Ukupni proračun" IZ Project GROUP BY ();
Korištenje parametra DISTINCT uklanja sve duplicirane vrijednosti u stupcu prije primjene funkcije SUM. Isto tako, uklonite sve NULL vrijednosti prije primjene ove agregatne funkcije.
Agregatna funkcija AVG
Agregat AVG funkcija vraća aritmetičku sredinu svih vrijednosti u stupcu. Argument ove agregatne funkcije uvijek mora biti numeričkog tipa podataka. Sve NULL vrijednosti se uklanjaju iz argumenta prije nego što se primijeni funkcija AVG.
Upotreba AVG agregatne funkcije prikazana je u primjeru u nastavku:
KORISTITE SampleDb; - Vraća 133833 SELECT AVG (proračun) "Prosječni proračun za projekt" IZ projekta;
Ovdje se izračunava aritmetički prosjek proračuna za sve proračune.
Zbirne funkcije COUNT i COUNT_BIG
Agregat COUNT funkcija ima dva različita oblika:
COUNT (naziv_stupca) COUNT (*)
Prvi oblik funkcije broji broj vrijednosti u stupcu col_name. Ako upit koristi ključnu riječ DISTINCT, sve duplicirane vrijednosti stupaca se uklanjaju prije upotrebe funkcije COUNT. Ovaj oblik funkcije COUNT ne uzima u obzir NULL vrijednosti pri prebrojavanju broja vrijednosti stupaca.
Upotreba prvog oblika agregatne funkcije COUNT prikazana je u primjeru u nastavku:
KORISTITE SampleDb; SELECT ProjectNumber, COUNT (DISTINCT Job) "Poslovi u projektu" FROM Works_on GRUPA PO Broju projekta;
Ovdje se broji broj različitih pozicija za svaki projekt. Rezultat ovog upita:
Kao što možete vidjeti iz upita u primjeru, NULL vrijednosti nisu uzete u obzir od strane funkcije COUNT. (Zbroj svih vrijednosti u stupcu posla je 7, a ne 11 kako bi trebao biti.)
Drugi oblik funkcije COUNT, t.j. funkcija COUNT (*) broji broj redaka u tablici. A ako izraz SELECT upita s funkcijom COUNT (*) sadrži klauzulu WHERE s uvjetom, funkcija vraća broj redaka koji zadovoljavaju navedeni uvjet. Za razliku od prve verzije funkcije COUNT, drugi oblik ne zanemaruje NULL vrijednosti jer ova funkcija radi na nizovima, a ne na stupcima. Primjer u nastavku pokazuje upotrebu funkcije COUNT (*):
KORISTITE SampleDb; ODABIR POSAO KAO "Vrsta posla", COUNT (*) "Potrebni radnici" IZ Works_on GRUPA PO POSAO;
Ovdje se računa broj postova u svim projektima. Rezultat izvršavanja upita:
COUNT_BIG funkcija ista funkcija kao COUNT. Jedina razlika između njih je u vrsti rezultata koji vraćaju: COUNT_BIG uvijek vraća BIGINT vrijednosti, dok COUNT vraća INTEGER vrijednosti podataka.
Statističke agregatne funkcije
Sljedeće funkcije čine skupinu statističkih agregatnih funkcija:
VARIzračunava statističku varijansu svih vrijednosti u stupcu ili izrazu.
VARPIzračunava statističku varijansu populacije svih vrijednosti u stupcu ili izrazu.
STDEVIzračunava standardnu devijaciju (koja se izračunava kao kvadratni korijen odgovarajuće varijance) svih vrijednosti u stupcu ili izrazu.
STDEVPIzračunava standardnu devijaciju zbirke svih vrijednosti u stupcu ili izrazu.
Korisnički definirane agregatne funkcije
Database Engine također podržava implementaciju korisnički definiranih funkcija. Ova mogućnost omogućuje korisnicima da dopune agregatne funkcije sustava funkcijama koje mogu sami implementirati i instalirati. Ove funkcije predstavljaju posebnu klasu korisnički definiranih funkcija i o njima će se kasnije raspravljati.
HAVING klauzula
U rečenici IMATI definira uvjet koji se primjenjuje na grupu redaka. Dakle, ova klauzula ima isto značenje za grupe redaka kao klauzula WHERE za sadržaj odgovarajuće tablice. Sintaksa za klauzulu HAVING je sljedeća:
IMATI stanje
Ovdje parametar uvjeta predstavlja uvjet i sadrži agregatne funkcije ili konstante.
Upotreba klauzule HAVING u sprezi sa agregatnom funkcijom COUNT (*) prikazana je u primjeru u nastavku:
KORISTITE SampleDb; - Vraća "p3" SELECT ProjectNumber FROM Works_on GROUP PO ProjectNumber HAVING COUNT (*)
U ovom primjeru, sustav koristi klauzulu GROUP BY za grupiranje svih redaka prema vrijednostima u stupcu ProjectNumber. Zatim se broji broj redaka u svakoj skupini i odabiru grupe koje sadrže manje od četiri retka (tri ili manje).
Klauzula HAVING također se može koristiti bez agregatnih funkcija, kao što je prikazano u primjeru u nastavku:
KORISTITE SampleDb; - Vratit će "Konsultant" ODABIR POSAO IZ Works_on GRUPA PO POSAO KOJI IMA POSAO KAO "K%";
Ovaj primjer grupira retke tablice Works_on prema naslovu posla i eliminira poslove koji ne počinju slovom "K".
Klauzula HAVING također se može koristiti bez klauzule GROUP BY, iako to nije uobičajena praksa. U ovom slučaju, svi se retki tablice vraćaju u istoj grupi.
ORDER BY klauzula
Rečenica NARUDŽITE PO određuje redoslijed sortiranja redaka u skupu rezultata koji vraća upit. Ova klauzula ima sljedeću sintaksu:
Redoslijed sortiranja naveden je u parametru col_name. Col_number je alternativni pokazatelj redoslijeda sortiranja koji identificira stupce redoslijedom kojim se pojavljuju na popisu odabira izraza SELECT (1 je prvi stupac, 2 drugi stupac i tako dalje). ASC parametar definira uzlazni red, i DESC parametar- nizvodno. Zadana postavka je ASC.
Nazivi stupaca u klauzuli ORDER BY ne moraju biti na popisu odabranih stupaca. Ali to se ne odnosi na upite poput SELECT DISTINCT, jer u takvim upitima, nazivi stupaca navedeni u klauzuli ORDER BY također se moraju pojaviti na popisu odabranih stupaca. Osim toga, ova klauzula ne može sadržavati nazive stupaca iz tablica koje nisu navedene u klauzuli FROM.
Kao što možete vidjeti iz sintakse ORDER BY klauzule, sortiranje skupa rezultata može se obaviti na više stupaca. Ovo sortiranje je prikazano u primjeru ispod:
U ovom primjeru odabiru se brojevi odjela i prezimena i imena zaposlenika za zaposlenike čiji je broj osoblja manji od 20.000, kao i sortirani po prezimenu i imenu. Rezultat ovog upita:
Stupci u klauzuli ORDER BY mogu se specificirati ne svojim nazivima, već redoslijedom na popisu odabira. Prema tome, rečenica u gornjem primjeru može se prepisati na sljedeći način:
Ovaj alternativni način određivanja stupaca po njihovoj poziciji umjesto po nazivima koristi se ako kriterij naručivanja sadrži agregatnu funkciju. (Drugi način je korištenje naziva stupaca koji se tada pojavljuju u klauzuli ORDER BY.) Međutim, u klauzuli ORDER BY preporučuje se da stupce navedete njihovim nazivima, a ne brojevima, kako biste lakše ažurirali upit ako trebate dodati ili ukloniti stupce na popisu za odabir. Određivanje stupaca u klauzuli ORDER BY njihovim brojevima prikazano je u primjeru ispod:
KORISTITE SampleDb; SELECT ProjectNumber, COUNT (*) "Broj zaposlenika" FROM Works_on GROUP BY ProjectNumber ORDER BY 2 DESC;
Ovdje se za svaki projekt odabire broj projekta i broj zaposlenika koji u njemu sudjeluju, slijeđujući rezultat silaznim redoslijedom prema broju zaposlenika.
Transact-SQL postavlja NULL vrijednosti na vrh popisa kada se sortiraju uzlaznim redoslijedom, a NULL vrijednosti na kraju popisa kada su razvrstane u silaznom redoslijedu.
Korištenje klauzule ORDER BY za paginiranje rezultata
Prikazivanje rezultata upita na trenutnoj stranici može se implementirati u prilagođenu aplikaciju ili možete naložiti poslužitelju baze podataka da to učini. U prvom slučaju svi se retki baze podataka šalju aplikaciji, čiji je zadatak odabrati tražene retke i prikazati ih. U drugom slučaju, samo retki potrebni za trenutnu stranicu se dohvaćaju i prikazuju sa strane poslužitelja. Kao što možete očekivati, stvaranje stranica na strani poslužitelja obično pruža najbolju izvedbu jer klijentu se šalju samo linije potrebne za prikaz.
Kako bi podržao kreiranje stranice na strani poslužitelja, SQL Server 2012 uvodi dvije nove klauzule SELECT: OFFSET i FETCH. Primjena ove dvije rečenice prikazana je u primjeru u nastavku. Ovdje, iz baze podataka AdventureWorks2012 (koju možete pronaći u izvoru), izdvaja poslovni ID, naziv radnog mjesta i rođendan svih zaposlenica, poredane po nazivu radnog mjesta uzlaznim redoslijedom. Rezultirajući skup redaka se dijeli na stranice od 10 redaka i prikazuje se treća stranica:
U rečenici OFFSET određuje broj redova rezultata koje treba preskočiti u prikazanom rezultatu. Ovaj iznos se izračunava nakon što se redci razvrstaju s klauzulom ORDER BY. U rečenici DOVEDI SLJEDEĆE specificira broj WHERE i sortiranih redaka koje treba vratiti. Parametar ove klauzule može biti konstanta, izraz ili rezultat drugog upita. Klauzula FETCH NEXT slična je klauzuli PRVI DOUZMI.
Glavni cilj pri stvaranju stranica na strani poslužitelja je biti u mogućnosti implementirati uobičajene obrasce stranica pomoću varijabli. Ovaj zadatak se može postići putem SQL Server paketa.
Izjava SELECT i svojstvo IDENTITY
IDENTITY svojstvo omogućuje vam da odredite vrijednosti za određeni stupac tablice u obliku automatski inkrementalnog brojača. Stupci numeričkog tipa podataka kao što su TINYINT, SMALLINT, INT i BIGINT mogu imati ovo svojstvo. Za takav stupac tablice, Database Engine automatski generira sekvencijalne vrijednosti počevši od navedene početne vrijednosti. Stoga se svojstvo IDENTITY može koristiti za generiranje jednoznamenkastih brojčanih vrijednosti za odabrani stupac.
Tablica može sadržavati samo jedan stupac sa svojstvom IDENTITY. Vlasnik tablice ima mogućnost odrediti početnu vrijednost i prirast, kao što je prikazano u primjeru ispod:
KORISTITE SampleDb; IZRADI TABLICU Proizvod (Id INT IDENTITETA (10000, 1) NIJE NULL, Naziv NVARCHAR (30) NOT NULL, Cijena NOVAC) UMETNI U VRIJEDNOSTI proizvoda (Naziv, Cijena) ("Proizvod1", 10), ("Proizvod2", 15) , ("Proizvod3", 8), ("Proizvod4", 15), ("Proizvod5", 40); - Vratit će 10004 SELECT IDENTITYCOL FROM Product WHERE Naziv = "Proizvod5"; - Analogno prethodnom iskazu SELECT $ identity FROM Product WHERE Naziv = "Proizvod5";
Ovaj primjer prvo stvara tablicu proizvoda koja sadrži stupac Id sa svojstvom IDENTITY. Vrijednosti u stupcu Id sustav kreira automatski, počevši od 10.000 i povećavajući se u jednom koraku za svaku sljedeću vrijednost: 10.000, 10.001, 10.002 itd.
Nekoliko funkcija sustava i varijabli pridruženo je svojstvu IDENTITY. Na primjer, primjer koda koristi varijabla sustava $ identitet... Kao što možete vidjeti iz rezultata pokretanja ovog koda, ova varijabla se automatski poziva na svojstvo IDENTITY. Umjesto toga možete koristiti i funkciju sustava IDENTITYCOL.
Početna vrijednost i prirast stupca sa svojstvom IDENTITY mogu se pronaći pomoću funkcija IDENT_SEED i IDENT_INCR odnosno. Ove funkcije se primjenjuju na sljedeći način:
KORISTITE SampleDb; SELECT IDENT_SEED ("Proizvod"), IDENT_INCR ("Proizvod")
Kao što je spomenuto, vrijednosti IDENTITY postavlja sustav automatski. Ali korisnik može eksplicitno odrediti njihove vrijednosti za određene nizove dodjeljivanjem parametru IDENTITY_INSERT vrijednost ON prije umetanja eksplicitne vrijednosti:
SET IDENTITY INSERT naziv tablice UKLJUČENO
Budući da se parametar IDENTITY_INSERT može koristiti za postavljanje bilo koje vrijednosti, uključujući duplicirane vrijednosti, na stupac sa svojstvom IDENTITY, svojstvo IDENTITY obično ne provodi jedinstvenost vrijednosti stupca. Stoga bi se trebala primijeniti ograničenja JEDINSTVENOG ili PRIMARNOG KLJUČA kako bi se nametnula jedinstvenost vrijednosti stupca.
Kada umetnete vrijednosti u tablicu nakon postavljanja IDENTITY_INSERT na uključeno, sustav stvara sljedeću vrijednost u stupcu IDENTITY, povećavajući najvišu trenutnu vrijednost u tom stupcu.
Izjava CREATE SEQUENCE
Korištenje svojstva IDENTITY ima nekoliko značajnih nedostataka, od kojih su najznačajniji:
svojstvo je ograničeno na navedenu tablicu;
nova vrijednost stupca ne može se dobiti ni na koji drugi način, osim primjenom;
svojstvo IDENTITY može se specificirati samo pri izradi stupca.
Iz tih razloga, SQL Server 2012 uvodi sekvence koje imaju istu semantiku kao svojstvo IDENTITY, ali bez prethodno navedenih nedostataka. U ovom kontekstu, slijed se odnosi na funkcionalnost baze podataka koja vam omogućuje da odredite vrijednosti brojača za različite objekte baze podataka, kao što su stupci i varijable.
Sekvence se stvaraju pomoću uputa STVORITE SEKVENCIJU... Izraz CREATE SEQUENCE definiran je u SQL standardu i podržan je od drugih sustava relacijskih baza podataka kao što su IBM DB2 i Oracle.
Primjer u nastavku pokazuje kako stvoriti slijed u SQL Serveru:
KORISTITE SampleDb; CREATE SEQUEENCE dbo.Sequence1 KAO INT POČINJE S 1 PORAVANJEM ZA 5 MINVRIJEDNOSTI 1 MAKSIMALNA VRIJEDNOST 256 CIKLUS;
U gornjem primjeru, vrijednosti Sequence1 generira sustav automatski, počevši od vrijednosti od 1 i u koracima od 5 za svaku uzastopnu vrijednost. Dakle, u klauzulu START naznačena je početna vrijednost, a in INCREMENT ponuda- korak. (Korak može biti pozitivan ili negativan.)
U sljedeće dvije neobavezne rečenice MINVRIJEDNOST i MAXVRIJEDNOST specificira minimalnu i maksimalnu vrijednost objekta sekvence. (Imajte na umu da MINVALUE mora biti manja ili jednaka početnoj vrijednosti, a MAXVALUE ne može biti veća od gornje granice vrste podataka specificirane za slijed.) U rečenici CIKLUS označava da se slijed ponavlja od početka kada se prekorači maksimalna (ili minimalna za niz s negativnim korakom) vrijednost. Prema zadanim postavkama, ova je klauzula postavljena na NO CYCLE, što znači da prekoračenje maksimalne ili minimalne vrijednosti sekvence izaziva iznimku.
Glavna značajka sekvenci je njihova neovisnost od tablica, t.j. mogu se koristiti s bilo kojim objektom baze podataka kao što su stupci tablice ili varijable. (Ovo svojstvo ima pozitivan učinak na pohranu, a time i na izvedbu. Ne treba pohranjivati određeni niz; pohranjuje se samo njegova posljednja vrijednost.)
Nove vrijednosti sekvence se kreiraju s SLJEDEĆA VRIJEDNOST ZA izraze, čija je primjena prikazana u primjeru ispod:
KORISTITE SampleDb; - Vraća 1 ODABIR SLJEDEĆE VRIJEDNOSTI ZA dbo.sequence1; - Vraća 6 (sljedeći korak) ODABIR SLJEDEĆE VRIJEDNOSTI ZA dbo.sequence1;
Možete koristiti izraz SLJEDEĆA VRIJEDNOST FOR da biste rezultat niza dodijelili varijabli ili ćeliji stupca. Primjer u nastavku ilustrira upotrebu ovog izraza za dodjelu rezultata stupcu:
KORISTITE SampleDb; CREATE TABLE Proizvod (Id INT NOT NULL, Naziv NVARCHAR (30) NOT NULL, Cijena NOVAC) UMETNI U VRIJEDNOSTI proizvoda (SLJEDEĆA VRIJEDNOST ZA dbo.sequence1, "Proizvod1", 10); UMETNI U VRIJEDNOSTI proizvoda (SLJEDEĆA VRIJEDNOST ZA dbo.sequence1, "Proizvod2", 15); - ...
Gornji primjer prvo stvara tablicu pod nazivom Proizvod s četiri stupca. Zatim dva INSERT izraza umeću dva retka u ovu tablicu. Prve dvije ćelije prvog stupca bit će 11 i 16.
Primjer u nastavku pokazuje korištenje kataloškog pogleda sys.sekvence za pregled trenutne vrijednosti niza bez upotrebe:
Obično se izraz SLJEDEĆA VRIJEDNOST FOR koristi u izrazu INSERT kako bi se sustav uputio da umetne generirane vrijednosti. Ovaj izraz se također može koristiti kao dio upita s više redaka pomoću klauzule OVER.
Za promjenu svojstva postojeće sekvence, primijenite Izjava ALTER SEQUENCE... Jedna od najvažnijih upotreba ove naredbe je opcija RESTART WITH, koja resetira navedeni slijed. Primjer u nastavku pokazuje upotrebu izraza ALTER SEQUENCE za resetiranje gotovo svih svojstava Sequence1:
KORISTITE SampleDb; ALTER SEQUENCE dbo.sequence1 PONOVNO POKRETANJE SA 100 POVEĆANJA ZA 50 MIN VRIJEDNOSTI 50 MAKSIMALNE VRIJEDNOSTI 200 BEZ CIKLUSA;
Slijed se briše pomoću upute DROP SEKVENCA.
Postavite operatore
Uz operatore o kojima smo ranije govorili, Transact-SQL podržava tri druga operatora skupa: UNION, INTERSECT i EXCEPT.
UNION operater
UNION operater kombinira rezultate dvaju ili više upita u jedan skup rezultata koji uključuje sve retke koji pripadaju svim upitima u uniji. Posljedično, rezultat spajanja dviju tablica je nova tablica koja sadrži sve retke u jednoj ili obje izvorne tablice.
Opći oblik UNION operatora izgleda ovako:
select_1 UNION select_2 (odaberi_3]) ...
Opcije select_1, select_2, ... su SELECT izjave koje stvaraju spajanje. Ako se koristi parametar ALL, prikazuju se svi reci, uključujući duplikate. U naredbi UNION, parametar ALL ima isto značenje kao u SELECT, s jednom razlikom: za SELECT, ovaj je parametar zadani, a za UNION mora biti eksplicitno specificiran.
U svom izvornom obliku, SampleDb nije prikladan za pokazivanje upotrebe UNION operatora. Stoga ovaj odjeljak stvara novu tablicu EmployeeEnh koja je identična postojećoj tablici Employee, ali ima dodatni stupac Grad. Ovaj stupac označava mjesto prebivališta zaposlenika.
Kreiranje tablice EmployeeEnh pruža nam priliku da demonstriramo upotrebu klauzule. U u naredbi SELECT. Naredba SELECT INTO izvodi dvije operacije. Prvo se kreira nova tablica sa stupcima navedenim na popisu SELECT. Zatim se redovi iz izvorne tablice ubacuju u novu tablicu. Ime nove tablice navedeno je u INTO klauzuli, a ime izvorne tablice navedeno je u klauzuli FROM.
Primjer u nastavku prikazuje kreiranje tablice EmployeeEnh iz tablice Employee:
KORISTITE SampleDb; SELECT * INTO EmployeeEnh FROM Employee; ALTER TABLE EmployeeEnh DODAJ Grad NCHAR (40) NULL;
U ovom primjeru, izraz SELECT INTO kreira tablicu EmployeeEnh, u nju umeće sve retke iz izvorne tablice Employee, a zatim izraz ALTER TABLE dodaje stupac Grad u novu tablicu. No dodani stupac Grad ne sadrži nikakve vrijednosti. Vrijednosti se mogu umetnuti u ovaj stupac kroz Management Studio ili korištenjem sljedećeg koda:
KORISTITE SampleDb; AŽURIRANJE EmployeeEnh SET Grad = "Kazan" GDJE Id = 2581; AŽURIRANJE EmployeeEnh SET Grad = "Moskva" GDJE Id = 9031; AŽURIRANJE EmployeeEnh SET Grad = "Jekaterinburg" GDJE Id = 10102; AŽURIRANJE EmployeeEnh SET Grad = "Sankt Peterburg" GDJE Id = 18316; UPDATE EmployeeEnh SET Grad = "Krasnodar" GDJE Id = 25348; UPDATE EmployeeEnh SET Grad = "Kazan" GDJE Id = 28559; AŽURIRAJ EmployeeEnh POSTAVI Grad = "Perm" GDJE Id = 29346;
Sada smo spremni pokazati korištenje izjave UNION. Primjer u nastavku prikazuje upit za stvaranje spajanja između tablica EmployeeEnh i Department pomoću ove izjave:
KORISTITE SampleDb; ODABERITE Grad KAO "Grad" IZ UNIONA EmployeeEnh ODABIRITE lokaciju IZ Odjela;
Rezultat ovog upita:
Pomoću naredbe UNION mogu se spojiti samo kompatibilne tablice. Pod kompatibilnim tablicama podrazumijevamo da oba popisa stupaca u odabiru moraju sadržavati isti broj stupaca, a odgovarajući stupci moraju imati kompatibilne tipove podataka. (U smislu kompatibilnosti, tipovi podataka INT i SMALLINT nisu kompatibilni.)
Rezultat konkatenacije može se poredati samo pomoću klauzule ORDER BY u posljednjoj SELECT naredbi, kao što je prikazano u primjeru ispod. Klauzule GROUP BY i HAVING mogu se koristiti s pojedinačnim naredbama SELECT, ali ne unutar samog spoja.
Upit u ovom primjeru odabire zaposlenike koji ili rade u odjelu d1 ili koji su počeli raditi na projektu prije 1. siječnja 2008.
Operator UNION podržava opciju SVE. Ova opcija ne uklanja duplikate iz skupa rezultata. OR operator se može koristiti umjesto UNION operatora ako se svi izrazi SELECT spojeni s jednim ili više UNION operatora odnose na istu tablicu. U ovom slučaju, skup naredbi SELECT zamjenjuje se jednim izrazom SELECT sa skupom izraza OR.
INTERSECT i EXCEPT operatori
Dva druga operatora za rad sa skupovima, PRESJEKATI i OSIM, definirati sjecište i razliku, respektivno. Ispod presjeka, u ovom kontekstu, nalazi se skup redaka koji pripadaju objema tablicama. A razlika između dvije tablice definira se kao sve vrijednosti koje pripadaju prvoj tablici, a nisu prisutne u drugoj. Primjer u nastavku pokazuje upotrebu operatora INTERSECT:
Transact-SQL ne podržava upotrebu parametra ALL s operatorima INTERSECT ili EXCEPT. Upotreba operatora EXCEPT prikazana je u primjeru ispod:
Zapamtite da ova tri operatora skupa imaju različit prioritet izvršenja: INTERSECT ima najveći prioritet, a slijedi EXCEPT, a UNION ima najmanji prioritet. Ne obraćanje pozornosti na prioritet izvršenja pri korištenju nekoliko različitih operatora skupa može dovesti do neočekivanih rezultata.
CASE izrazi
U području aplikacijskog programiranja baze podataka ponekad je potrebno modificirati prikaz podataka. Na primjer, ljudi se mogu podijeliti prema njihovoj društvenoj klasi, koristeći vrijednosti 1, 2 i 3, koje označavaju muškarce, žene i djecu. Ova tehnika programiranja može smanjiti vrijeme potrebno za implementaciju programa. CASE izraz Transact-SQL vam omogućuje jednostavnu implementaciju ove vrste kodiranja.
Za razliku od većine programskih jezika, CASE nije izjava, već izraz. Stoga se izraz CASE može koristiti gotovo svugdje gdje Transact-SQL dopušta korištenje izraza. Izraz CASE ima dva oblika:
jednostavan CASE izraz;
izraz za pretraživanje CASE.
Sintaksa za jednostavan CASE izraz je sljedeća:
Naredba s jednostavnim CASE izrazom prvo pretražuje popis svih izraza klauzula WHEN prvi izraz koji odgovara izrazu_1, a zatim izvršava odgovarajući TADA klauzula... Ako na popisu WHEN ne postoji odgovarajući izraz, onda ELSE klauzula.
Sintaksa za CASE izraz za pretraživanje je sljedeća:
U ovom slučaju, traži prvi kvalificirajući uvjet, a zatim izvršava odgovarajuću klauzulu THEN. Ako niti jedan od uvjeta ne ispunjava uvjete, izvršava se klauzula ELSE. Upotreba izraza za pretraživanje CASE prikazana je u primjeru ispod:
KORISTITE SampleDb; ODABIR Naziv projekta, SLUČAJ KADA Proračun> 0 I Proračun 100000 I Proračun 150000 I Proračun
Rezultat ovog upita:
Ovaj primjer ponderira proračune svih projekata i prikazuje izračunate težine zajedno s odgovarajućim nazivima projekata.
Primjer u nastavku pokazuje drugi način korištenja izraza CASE gdje klauzula WHEN sadrži potupite koji su dio izraza:
KORISTITE SampleDb; ODABIR Naziv projekta, SLUČAJ KADA p1.Proračun (ODABIR PROSJ. (p2.Proračun) IZ Projekta p2) ONDA "iznad prosjeka" KRAJ "Kategorija proračuna" IZ Projekta p1;
Rezultat ovog upita je sljedeći:
Za sažetak informacija sadržanih u bazi podataka, SQL pruža agregatne funkcije. Agregatna funkcija uzima cijeli stupac podataka kao argument i vraća jednu vrijednost koja sažima taj stupac na specifičan način.
Na primjer, agregatna funkcija AVG () uzima stupac brojeva kao argument i izračunava njihov prosjek.
Za izračunavanje prosječnog dohotka po glavi stanovnika Zelenograda potreban vam je sljedeći upit:
ODABIR 'PROSJEČNI PRIHODA =', AVG (SUMD)
SQL ima šest agregatnih funkcija koje pružaju različite vrste sažetaka (slika 1):
- SUM () izračunava zbroj svih vrijednosti sadržanih u stupcu;
- AVG () izračunava prosjek između vrijednosti sadržanih u stupcu;
- MIN () pronalazi najmanju među svim vrijednostima sadržanim u stupcu;
- MAX () pronalazi najveću među svim vrijednostima sadržanim u stupcu;
- COUNT () broji broj vrijednosti sadržanih u stupcu;
- COUNT (*) broji broj redaka u tablici rezultata upita.
Argument agregatnoj funkciji može biti jednostavno ime stupca, kao u prethodnom primjeru, ili izraz, kao u sljedećem upitu koji izračunava porez po glavi stanovnika:
ODABIR PROSJEČNOG (SUMD * 0,13)
Ovaj upit stvara privremeni stupac koji sadrži vrijednosti (SUMD * 0,13) za svaki redak u tablici PERSON, a zatim izračunava prosjek privremenog stupca.
Zbroj prihoda svih stanovnika Zelenograda može se izračunati pomoću agregatne funkcije SUM:
ODABERITE ZUM (SUMD) OD OSOBE
Agregatna funkcija također se može koristiti za izračunavanje ukupnih vrijednosti za tablicu rezultata dobivenih spajanjem nekoliko izvornih tablica. Na primjer, možete izračunati ukupan iznos prihoda koji stanovnici primaju iz izvora pod nazivom "Stipendija":
ODABIR ZVOTA (NOVAC)
OD PROFITA, HAVE_D
WHERE PROFIT.ID = HAVE_D.ID
AND PROFIT.SOURCE = 'Stipendija'
Funkcije agregata MIN () i MAX () omogućuju vam da pronađete najmanju i najveću vrijednost u tablici. Međutim, stupac može sadržavati numeričke ili nizove vrijednosti ili vrijednosti datuma ili vremena.
Na primjer, možete definirati:
(a) najniži ukupni dohodak rezidenti i najveći porez koji se plaća:
ODABIR MIN (SUMD), MAX (SUMD * 0,13)
(b) datumi rođenja najstarijeg i najmlađeg stanovnika:
ODABIR MIN (RDATE), MAX (RDATE)
(c) prezimena, imena i patronimika prvih i zadnjih stanovnika na popisu, poredani po abecedi:
ODABIR MIN (FIO), MAX (FIO)
Prilikom primjene ovih agregatnih funkcija, morate imati na umu da se numerički podaci uspoređuju prema aritmetičkim pravilima, datumi se uspoređuju uzastopno (ranije vrijednosti datuma smatraju se manjim od kasnijih), vremenski intervali se uspoređuju na temelju njihovog trajanja.
Kada koristite MIN () i MAX () s podacima niza, rezultat usporedbe dvaju nizova ovisi o korištenoj tablici kodiranja znakova.
Agregatna funkcija COUNT () broji broj vrijednosti u stupcu bilo koje vrste:
(a) koliko stanova ima u 1. mikrookrug?
ODABIR BROJ (ADR) IZ RAVNOG GDJE ADR KAO "%, 1_ _-%"
(b) koliko stanovnika ima izvore prihoda?
ODABIR BROJ (DISTINCT NOM) OD HAVE_D
(c) koliko izvora prihoda koriste stanovnici?
SELECT COUNT (DISTINCT ID) FROM HAVE_D (Ključna riječ DISTINCT određuje da se broje neduplicirane vrijednosti u stupcu).
Posebna agregatna funkcija COUNT (*) broji retke u tablici rezultata, a ne vrijednosti podataka:
(a) koliko stanova ima u 2. mikrookrug?
ODABIR BROJ (*) IZ RAVNOG GDJE ADR KAO "%, 2 __-%"
(b) koliko izvora prihoda ima Ivan Ivanovič?
ODABIR BROJ (*) OD OSOBE, HAVE_D GDJE FIO = "Ivanov Ivan Ivanovič" I PERSON.NOM = HAVE_D.NOM
(c) koliko stanovnika živi u stanu na određenoj adresi?
ODABIR BROJ (*) OD OSOBE GDJE ADR = "Zelenograd, 1001-45"
Jedan od načina da shvatite kako se agregatni upiti izvode je razmišljanje o izvršenju upita u dva dijela. Prvo se utvrđuje kako bi upit funkcionirao bez agregatnih funkcija, vraćajući više redaka rezultata. Agregatne funkcije se zatim primjenjuju na rezultate upita, vraćajući jedan redak sažetka.
Na primjer, razmotrite sljedeći složeni upit: pronađite prosječni ukupni dohodak po stanovniku, zbroj ukupnog prihoda stanovnika i prosječni prihod izvora kao postotak ukupnog prihoda stanovnika. Odgovor daje operater
ODABIR PROSJEČAN (SUMD), SUM (SUMD), (100 * AVG (NOVAC / SUMD)) OD OSOBE, PROFIT, HAVE_D GDJE PERSON.NOM = HAVE_D.NOM I HAVE_D.ID = PROFIT.ID
Bez agregatnih funkcija, upit bi izgledao ovako:
ODABIR SUMD, SUMD, NOVAC / SUMD OD OSOBE, PROFIT, HAVE_D GDJE PERSON.NOM = HAVE_D.NOM I HAVE_D.ID = PROFIT.ID
i vratio bi jedan red rezultata za svakog stanovnika i određeni izvor prihoda. Zbirne funkcije koriste stupce u tablici rezultata ovog upita kako bi proizvele tablicu u jednom redu sa sažetim rezultatima.
U retku vraćenih stupaca možete odrediti agregatnu funkciju umjesto naziva bilo kojeg stupca. Na primjer, može se uključiti u izraz koji dodaje ili oduzima vrijednosti dviju agregatnih funkcija:
ODABERITE MAX (SUMD) -MIN (SUMD) OD OSOBE
Međutim, agregatna funkcija ne može biti argument drugoj agregatnoj funkciji, tj. ugniježđene agregatne funkcije su zabranjene.
Osim toga, agregatne funkcije i redoviti nazivi stupaca ne mogu se koristiti na popisu vraćenih stupaca u isto vrijeme, jer u tome nema smisla, na primjer:
ODABERITE FIO, SUM (SUMD) OD OSOBE
Ovdje prvi element popisa daje upute DBMS-u da kreira tablicu koja će se sastojati od nekoliko redaka i sadržavati jedan red za svakog korisnika. Druga stavka na popisu traži od DBMS-a da dobije jednu vrijednost rezultata, a to je zbroj vrijednosti u stupcu SUMD. Ove dvije smjernice proturječe jedna drugoj, što rezultira pogreškom.
Gore navedeno ne vrijedi za slučajeve obrade potupita i upita s grupiranjem.
