GROUP BY ponuda(Izraz SELECT) vam omogućava grupiranje podataka (redova) prema vrijednosti stupca ili više stupaca ili izraza. Rezultat će biti skup sažetih redova.
Svaki stupac na listi za odabir mora biti prisutan u klauzuli GROUP BY, osim za konstante i stupce koji su operandi agregatnih funkcija.
Tabela se može grupirati prema bilo kojoj kombinaciji svojih kolona.
Agregatne funkcije se koriste za dobivanje jedne zbirne vrijednosti iz grupe redova. Sve agregatne funkcije izvode izračune na jednom argumentu, koji može biti ili stupac ili izraz. Rezultat bilo koje agregatne funkcije je konstantna vrijednost prikazana u zasebnoj koloni rezultata.
Agregatne funkcije su specificirane u listi stupaca izraza SELECT, koji također može sadržavati klauzulu GROUP BY. Ako u SELECT izrazu nema klauzule GROUP BY, a lista odabranih stupaca sadrži, by najmanje, jedna agregatna funkcija, onda ne bi trebao sadržavati jednostavne stupce. S druge strane, lista za odabir stupaca može sadržavati nazive stupaca koji nisu argumenti agregatnoj funkciji ako su ti stupci argumenti klauzule GROUP BY.
Ako upit sadrži klauzulu WHERE, tada agregatne funkcije izračunavaju vrijednost za rezultate odabira.
Agregatne funkcije MIN i MAX izračunajte najmanji i najveća vrijednost kolone respektivno. Argumenti mogu biti brojevi, nizovi i datumi. Sve NULL vrijednosti se brišu prije izračuna (tj. ne uzimaju se u obzir).
Agregatna funkcija SUM izračunava ukupan iznos vrijednosti stupaca. Argumenti mogu biti samo brojevi. Korištenje opcije DISTINCT eliminira sve duple vrijednosti u koloni prije primjene SUM funkcije. Slično, sve NULL vrijednosti se uklanjaju prije primjene ove agregatne funkcije.
Agregat AVG funkcija vraća prosjek svih vrijednosti u koloni. Argumenti također mogu biti samo brojevi, a sve NULL vrijednosti se uklanjaju prije evaluacije.
Agregatna funkcija COUNT ima dva različita oblika:
- COUNT( col_name) - broji broj vrijednosti u koloni Col_name, NULL vrijednosti se zanemaruju
- COUNT(*) - broji broj redova u tabeli, uzimaju se u obzir i NULL vrijednosti
Ako se u upitu koristi ključna riječ DISTINCT, sve duplicirane vrijednosti stupaca se uklanjaju prije nego što se primijeni funkcija COUNT.
COUNT_BIG funkcija slično funkciji COUNT. Jedina razlika između njih je tip rezultata koji vraćaju: funkcija COUNT_BIG uvijek vraća BIGINT vrijednosti, dok funkcija COUNT vraća INTEGER vrijednosti podataka.
V HAVING ponudu definira uvjet koji se primjenjuje na grupu redova. Ima isto značenje za grupe redova kao klauzula WHERE za sadržaj odgovarajuće tabele (WHERE se primenjuje pre grupisanja, HAVING posle).
Može proizvesti generalizovano serijska obrada vrijednosti polja. Ovo se radi pomoću agregatnih funkcija. Agregatne funkcije proizvode jednu vrijednost za cijelu grupu tablica. SQL pruža sljedeće agregatne funkcije:
- COUNT– broji broj redova tablice s vrijednostima koje nisu NULL u polju navedenom kao argument.
- SUMA– izračunava aritmetički zbir svih odabranih vrijednosti datog polja.
- AVG– prosječuje sve odabrane vrijednosti datog polja.
- MAX- Prikazuje najveću vrijednost od svih odabranih vrijednosti datog polja.
- MIN– Prikazuje najmanju vrijednost od svih odabranih vrijednosti u datom polju.
Korištenje agregatnih funkcija
Agregatne funkcije se koriste kao imena polja u SELECT klauzuli upita, s jednim izuzetkom: uzimaju imena polja kao argument. Mogu se koristiti samo numerička polja SUMA i AVG. WITH COUNT, MAX, i MIN mogu se koristiti i numerička i karakterna polja. Kada se koristi sa znakovnim poljima MAX i MINće ih prevesti u njihov ASCII ekvivalent. To znači da MINće izabrati prvu, i MAX zadnja vrijednost po abecednom redu.
Da bismo pronašli ukupan iznos prodaje u tabeli prodaje, moramo napisati sljedeći upit:
SELECT SUM(SSum) IZ Prodaje
Kao rezultat, dobijamo:
Ovaj upit je izbrojao broj nepraznih vrijednosti u polju SNum tabele Sells. Ako prepišemo upit na sljedeći način:
SELECT COUNT(SDate) FROM Sells
Tada kao rezultat dobijamo:
COUNT OF SDate 4 Različiti rezultati upita pri izračunavanju naizgled iste stvari se dobijaju jer jedna od vrijednosti polja SDate ima prazna vrijednost (NULL). Budite oprezni kada koristite takve zahtjeve.
Kako mogu saznati broj modela PC-a koje proizvodi određeni proizvođač? Kako odrediti prosječnu cijenu računara koji imaju istu specifikacije? Ova i mnoga druga pitanja vezana su za neke statističke informacije, možete dobiti odgovore koristeći zbirne (agregatne) funkcije. Standard pruža sljedeće agregatne funkcije:
Sve ove funkcije vraćaju jednu vrijednost. Istovremeno, funkcije COUNT, MIN i MAX primijeniti na bilo koji tip podataka, dok SUMA i AVG koristi se samo za numerička polja. Razlika između funkcija COUNT(*) i COUNT(<имя поля>) je da drugi ne uzima u obzir NULL vrijednosti prilikom izračunavanja.
Primjer. Pronađite minimalnu i maksimalnu cijenu za personalne računare:
Primjer. Pronađite raspoloživi broj računara proizvođača A:
Primjer. Ako nas zanima količina razni modeli proizveden od strane proizvođača A, onda se upit može formulirati na sljedeći način (koristeći činjenicu da je svaki model jednom zabilježen u tablici proizvoda):
Primjer. Pronađite broj dostupnih različitih modela proizvođača A. Upit je sličan prethodnom, u kojem je bilo potrebno odrediti ukupan broj modeli proizvedeni od strane proizvođača A. Ovde takođe treba da pronađete broj različitih modela u tabeli računara (odnosno, komercijalno dostupnih).
Kako bi se osiguralo da se pri dobijanju statističkih pokazatelja koriste samo jedinstvene vrijednosti, kada argument agregatne funkcije može biti korišteno DISTINCT parametar. Drugi parametar SVE je zadana vrijednost i očekuje da će brojati sve povratne vrijednosti u stupcu. operater,
Ako trebamo dobiti broj PC modela proizvedenih od strane svaki proizvođača, morat ćete koristiti GROUP BY ponuda, sintaktički slijedi nakon WHERE klauzule.
GROUP BY ponuda
GROUP BY ponuda koristi se za definiranje grupa izlaznih linija na koje se može primijeniti agregatne funkcije (COUNT, MIN, MAX, AVG i SUM). Ako ova klauzula nedostaje i ako se koriste agregatne funkcije, tada se koriste svi stupci s imenima navedenim u SELECT, treba uključiti u agregatne funkcije, a ove funkcije će se primijeniti na cijeli skup redova koji zadovoljavaju predikat upita. Inače, sve kolone SELECT liste, nisu uključeni u agregatne funkcije, mora biti specificirano u klauzuli GROUP BY. Kao rezultat toga, svi izlazni redovi upita podijeljeni su u grupe koje karakteriziraju iste kombinacije vrijednosti u ovim stupcima. Nakon toga, agregatne funkcije će se primijeniti na svaku grupu. Imajte na umu da se za GROUP BY sve NULL vrijednosti tretiraju kao jednake, tj. kada se grupiše po polju koje sadrži NULL vrijednosti, svi takvi redovi će pasti u jednu grupu.Ako ako postoji klauzula GROUP BY, u klauzuli SELECT nema agregatnih funkcija, onda će upit jednostavno vratiti jedan red iz svake grupe. Ova funkcija, zajedno sa ključnom riječi DISTINCT, može se koristiti za eliminaciju duplih redova u skupu rezultata.
Razmotrite jednostavan primjer:
| SELECT model, COUNT(model) AS Qty_model, AVG(cijena) AS prosječna cijena SA PC-a GROUP BY model; |
U ovom upitu, za svaki model računara, određuje se njihov broj i prosječna cijena. Sve linije sa iste vrijednosti model (broj modela) formiraju grupu, a SELECT izlaz izračunava broj vrijednosti i vrijednosti prosječne cijene za svaku grupu. Rezultat upita bit će sljedeća tabela:
| model | Qty_model | Prosječna cijena |
| 1121 | 3 | 850.0 |
| 1232 | 4 | 425.0 |
| 1233 | 3 | 843.33333333333337 |
| 1260 | 1 | 350.0 |
Da postoji kolona sa datumom u SELECT, tada bi bilo moguće izračunati ove indikatore za svaki određeni datum. Da biste to učinili, trebate dodati datum kao kolonu za grupisanje, a zatim bi se agregatne funkcije izračunale za svaku kombinaciju vrijednosti (model-datum).
Postoji nekoliko specifičnih pravila za izvršavanje agregatnih funkcija:
- Ako kao rezultat zahtjeva nije primljen nijedan red(ili više od jednog reda za ovu grupu), tada nema početnih podataka za izračunavanje bilo koje od agregatnih funkcija. U ovom slučaju, rezultat izvršavanja COUNT funkcija bit će nula, a rezultat svih ostalih funkcija će biti NULL.
- Argument agregatna funkcija ne može sama sadržavati agregatne funkcije(funkcija od funkcije). One. u jednom zahtjevu nemoguće je, recimo, dobiti maksimum prosječnih vrijednosti.
- Rezultat izvršavanja funkcije COUNT je cijeli broj(INTEGER). Druge agregatne funkcije nasljeđuju tipove podataka obrađenih vrijednosti.
- Ako se prilikom izvršavanja funkcije SUM dobije rezultat koji premašuje maksimalnu vrijednost korištenog tipa podataka, a greška.
Dakle, ako zahtjev ne sadrži GROUP BY nudi, onda agregatne funkcije uključeno u SELECT klauzula, izvršavaju se na svim rezultirajućim nizovima upita. Ako zahtjev sadrži GROUP BY ponuda, svaki skup redova koji ima iste vrijednosti stupca ili grupe kolona navedenih u ponuda GROUP BY, čini grupu, i agregatne funkcije izvodi se za svaku grupu posebno.
HAVING ponudu
Ako WHERE klauzula zatim definira predikat za filtriranje nizova HAVING klauzula primijenjeno nakon grupisanja definirati slične grupe za filtriranje predikata po vrijednostima agregatne funkcije. Ova klauzula je potrebna za validaciju vrijednosti koje su dobivene pomoću agregatna funkcija ne iz zasebnih redova izvora zapisa definiranog u FROM klauzula, i od grupe takvih linija. Stoga takva provjera ne može biti sadržana u WHERE klauzula.
Sljedeći pododjeljci opisuju druge ponude SELECT izraz, koji se može koristiti u upitima, kao i agregatne funkcije i skupovi operatora. Dozvolite mi da vas podsjetim ovog trenutka pogledali smo upotrebu klauzule WHERE, au ovom članku ćemo pogledati klauzule GROUP BY, ORDER BY i HAVING i dati neke primjere korištenja ovih klauzula u sprezi sa agregatnim funkcijama koje su podržane u Transact-u. SQL.
GROUP BY ponuda
Rečenica GROUP BY grupiše odabrani skup redova da proizvede skup sažetih redova na osnovu vrijednosti jedne ili više kolona ili izraza. Jednostavna upotreba klauzule GROUP BY prikazana je u primjeru ispod:
USE SampleDb; SELECT Job FROM Works_On GROUP BY Job;
U ovom primjeru, pozicije zaposlenika su odabrane i grupisane.
U gornjem primjeru stvara se klauzula GROUP BY odvojena grupa za sve moguće vrijednosti(uključujući null vrijednost) kolone Posao.
Upotreba stupaca u klauzuli GROUP BY mora ispunjavati određene uvjete. Konkretno, svaka kolona na listi odabira upita također se mora pojaviti u klauzuli GROUP BY. Ovaj zahtjev se ne odnosi na konstante i stupce koji su dio agregatne funkcije. (O agregatnim funkcijama se govori u sljedećem pododjeljku.) Ovo ima smisla jer samo kolone u klauzuli GROUP BY imaju zagarantovanu jednu vrijednost po grupi.
Tabela se može grupirati prema bilo kojoj kombinaciji svojih kolona. Primjer ispod pokazuje grupiranje redova Works_on tablice u dvije kolone:
USE SampleDb; SELECT ProjectNumber, Job FROM Works_On GROUP BY ProjectNumber, Job;
Rezultat ovog upita je:
Na osnovu rezultata upita možete vidjeti da postoji devet grupa s različitim kombinacijama broja projekta i pozicije. Redoslijed imena stupaca u klauzuli GROUP BY ne mora biti isti kao na listi stupaca SELECT.
Agregatne funkcije
Agregatne funkcije se koriste za dobivanje ukupnih vrijednosti. Sve agregatne funkcije mogu se podijeliti u sljedeće kategorije:
obične agregatne funkcije;
statističke agregatne funkcije;
agregatne funkcije definirane od strane korisnika;
analitičke agregatne funkcije.
Ovdje ćemo pogledati prve tri vrste agregatnih funkcija.
Obične agregatne funkcije
Transact-SQL jezik podržava sljedećih šest agregatnih funkcija: MIN, MAX, SUMA, AVG, COUNT, COUNT_BIG.
Sve agregatne funkcije izvode izračune na jednom argumentu, koji može biti ili stupac ili izraz. (Jedini izuzetak je drugi oblik dvije funkcije COUNT i COUNT_BIG, odnosno COUNT(*) i COUNT_BIG(*).) Rezultat bilo koje agregatne funkcije je konstantna vrijednost prikazana u zasebnoj koloni rezultata.
Agregatne funkcije navedene su u listi stupaca izraza SELECT, koja također može sadržavati klauzulu GROUP BY. Ako nema GROUP BY klauzule u SELECT izrazu, a lista stupaca za odabir sadrži barem jednu agregatnu funkciju, onda ne smije sadržavati jednostavne stupce (osim stupaca koji služe kao argumenti agregatnoj funkciji). Stoga je kod u primjeru ispod netačan:
USE SampleDb; SELECT Prezime, MIN(Id) FROM Employee;
Ovdje kolona Prezime u tablici Employee ne bi trebala biti na listi za odabir stupca jer nije argument agregatne funkcije. S druge strane, lista za odabir stupaca može sadržavati nazive stupaca koji nisu argumenti agregatnoj funkciji ako su ti stupci argumenti klauzule GROUP BY.
Argumentu agregatne funkcije može prethoditi jedan od dva moguća ključne riječi:
SVEOdređuje da se proračuni izvode za sve vrijednosti u koloni. Ovo je zadana vrijednost.
DISTINCTOdređuje da samo jedinstvene vrijednosti kolona.
Agregatne funkcije MIN i MAX
Agregatne funkcije MIN i MAX izračunavaju najmanju i najveću vrijednost stupca, respektivno. Ako upit sadrži klauzulu WHERE, funkcije MIN i MAX vraćaju najmanju i najveću vrijednost redaka koji ispunjavaju navedene kriterije. Primjer u nastavku pokazuje upotrebu agregatne funkcije MIN:
USE SampleDb; -- Vraća 2581 SELECT MIN(Id) AS "Min Id" FROM Employee;
Rezultat vraćen u gornjem primjeru nije baš informativan. Na primjer, ime zaposlenika koji posjeduje ovaj broj nije poznato. Međutim, nije moguće dobiti ovo prezime na uobičajen način, jer, kao što je ranije spomenuto, eksplicitno navođenje stupca Prezime nije dozvoljeno. Da bi se dobilo prezime ovog zaposlenog uz najmanji kadrovski broj zaposlenog, koristi se potupit. Sljedeći primjer pokazuje upotrebu takvog potupita, gdje potupit sadrži naredbu SELECT iz prethodnog primjera:
Rezultat izvršenja upita:
Upotreba MAX agregatne funkcije prikazana je u primjeru ispod:
Funkcije MIN i MAX također mogu uzeti nizove i datume kao argumente. U slučaju argumenta string, vrijednosti se uspoređuju koristeći stvarni redoslijed sortiranja. Za sve argumente privremenih podataka tipa "datum", najmanja vrijednost stupca će biti najveća rani datum, a najveći - najnoviji.
Možete koristiti ključnu riječ DISTINCT sa funkcijama MIN i MAX. Prije nego što se koriste agregatne funkcije MIN i MAX, sve NULL vrijednosti se isključuju iz njihovih kolona argumenata.
Agregatna funkcija SUM
Agregat Funkcija SUM izračunava ukupni zbir vrijednosti stupca. Argument ove agregatne funkcije uvijek mora biti numeričkog tipa podataka. Upotreba agregatne funkcije SUM prikazana je u primjeru ispod:
USE SampleDb; SELECT SUM (Budget) "Summary budget" IZ projekta;
Ovaj primjer izračunava ukupan zbir budžeta svih projekata. Rezultat izvršenja upita:
U ovom primjeru, agregatna funkcija grupiše sve vrijednosti budžeta projekta i određuje njihov ukupan iznos. Iz tog razloga, upit sadrži implicitnu funkciju grupiranja (kao i svi slični upiti). Implicitna funkcija grupisanja iz gornjeg primjera može se eksplicitno specificirati, kao što je prikazano u primjeru ispod:
USE SampleDb; SELECT SUM (Budžet) "Ukupni budžet" IZ Project GROUP BY();
Korištenje opcije DISTINCT eliminira sve duple vrijednosti u stupcu prije primjene funkcije SUM. Slično, sve NULL vrijednosti se uklanjaju prije primjene ove agregatne funkcije.
AVG agregatna funkcija
Agregat AVG funkcija vraća aritmetičku sredinu svih vrijednosti u koloni. Argument ove agregatne funkcije uvijek mora biti numeričkog tipa podataka. Prije upotrebe AVG funkcije, sve NULL vrijednosti se uklanjaju iz njenog argumenta.
Upotreba AVG agregatne funkcije prikazana je u primjeru ispod:
USE SampleDb; -- Vraća 133833 SELECT AVG (budžet) "Prosečan budžet po projektu" IZ projekta;
Ovdje se izračunava prosjek. aritmetička vrijednost budžet za sve budžete.
Agregatne funkcije COUNT i COUNT_BIG
Agregat COUNT funkcija ima dva različita oblika:
COUNT(naziv_kolca) COUNT(*)
Prvi oblik funkcije broji broj vrijednosti u stupcu col_name. Ako se u upitu koristi ključna riječ DISTINCT, sve duplicirane vrijednosti stupaca se uklanjaju prije nego što se primijeni funkcija COUNT. Ovaj oblik funkcije COUNT ne uzima u obzir NULL vrijednosti kada se broji broj vrijednosti u stupcu.
Upotreba prvog oblika agregatne funkcije COUNT prikazana je u primjeru ispod:
USE SampleDb; SELECT ProjectNumber, COUNT(DISTINCT Job) "Radi u projektu" FROM Works_on GROUP BY ProjectNumber;
Ovdje se računa broj različitih pozicija za svaki projekat. Rezultat ovog upita je:
Kao što možete vidjeti iz primjera upita, NULL vrijednosti nisu uzete u obzir od strane funkcije COUNT. (Zbroj svih vrijednosti u stupcu posla pokazao se kao 7, a ne 11, kako bi trebao biti.)
Drugi oblik funkcije COUNT, tj. funkcija COUNT(*) broji broj redova u tabeli. A ako izraz SELECT upita s funkcijom COUNT(*) sadrži klauzulu WHERE s uvjetom, funkcija vraća broj redova koji zadovoljavaju navedeni uvjet. Za razliku od prvog oblika funkcije COUNT, drugi oblik ne zanemaruje NULL vrijednosti jer ova funkcija djeluje na redove, a ne na stupce. Primjer ispod pokazuje korištenje funkcije COUNT(*):
USE SampleDb; SELECT Posao KAO "Vrsta posla", COUNT(*) "Potrebni radnici" FROM Works_on GRUPA PO POSAO;
Ovdje se računa broj pozicija u svim projektima. Rezultat izvršenja upita:
COUNT_BIG funkcija slično funkciji COUNT. Jedina razlika između njih je tip rezultata koji vraćaju: funkcija COUNT_BIG uvijek vraća BIGINT vrijednosti, dok funkcija COUNT vraća INTEGER vrijednosti podataka.
Statističke agregatne funkcije
Sljedeće funkcije čine grupu statističkih agregatnih funkcija:
VARIzračunava statističku varijansu svih vrijednosti predstavljenih u stupcu ili izrazu.
VARPIzračunava statističku varijansu populacije svih vrijednosti predstavljenih u stupcu ili izrazu.
STDEVIzračunava standardnu devijaciju (koja se izračunava kao Kvadratni korijen iz odgovarajuće varijanse) svih vrijednosti stupca ili izraza.
STDEVPIzračunava standardnu devijaciju ukupnosti 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ćava korisnicima da prošire sistemske agregatne funkcije funkcijama koje mogu sami implementirati i instalirati. Ove funkcije predstavljaju posebnu klasu korisnički definiranih funkcija i o njima se kasnije govori detaljnije.
HAVING ponudu
U rečenici HAVING definira uvjet koji se primjenjuje na grupu redova. Dakle, ova klauzula ima isto značenje za grupe redova kao klauzula WHERE za sadržaj odgovarajuće tabele. Sintaksa IMATI ponude sljedeći:
HAVING stanje
Ovdje parametar uvjeta predstavlja uvjet i sadrži agregatne funkcije ili konstante.
Upotreba klauzule HAVING sa agregatnom funkcijom COUNT(*) prikazana je u primjeru ispod:
USE SampleDb; -- Vrati "p3" SELECT ProjectNumber FROM Works_on GRUPA PO Broju projekta IMA COUNT(*)
U ovom primjeru, koristeći klauzulu GROUP BY, sistem grupiše sve redove na osnovu vrijednosti u stupcu ProjectNumber. Nakon toga, broj redova u svakoj grupi se broji i odabiru grupe koje sadrže manje od četiri reda (tri ili manje).
Klauzula HAVING se također može koristiti bez agregatnih funkcija, kao što je prikazano u primjeru ispod:
USE SampleDb; -- Vraća "Konsultant" ODABIR POSAO IZ Works_on GRUPE PO POSLU KOJI IMA POSAO KAO "K%";
Ovaj primjer grupiše redove u tabeli Works_on po poziciji i eliminiše one pozicije koje ne počinju slovom "K".
Klauzula HAVING se također može koristiti bez klauzule GROUP BY, iako to nije uobičajena praksa. U ovom slučaju, svi redovi tabele se vraćaju u istoj grupi.
NARUČI PO ponudi
Rečenica POREDAK PO definira redoslijed sortiranja redova u skupu rezultata koji je vratio upit. Ova rečenica ima sljedeću sintaksu:
Redoslijed sortiranja je specificiran u parametru col_name. Parametar col_number je alternativni specificator redoslijeda sortiranja koji specificira stupce redoslijedom kojim se pojavljuju na listi odabira izraza SELECT (1 je prvi stupac, 2 je drugi stupac, itd.). ASC parametar definira sortiranje uzlaznim redoslijedom, i DESC parametar- silazno. Podrazumevano je ASC.
Imena stupaca u klauzuli ORDER BY ne moraju biti na popisu stupaca za odabir. Ali ovo se ne odnosi na SELECT DISTINCT upite, jer u takvim upitima, nazivi stupaca specificirani u ORDER BY klauzuli također moraju biti specificirani u listi za odabir 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, skup rezultata se može sortirati na više kolona. Ovo sortiranje je prikazano u primjeru ispod:
Ovaj primjer odabire brojeve odjela i prezimena i imena zaposlenika za zaposlenike čiji je broj osoblja manji od 20.000 i sortira prema prezimenu i imenu. Rezultat ovog upita je:
Kolone u ORDER BY klauzuli mogu se specificirati ne njihovim imenima, već redoslijedom na listi za odabir. Shodno tome, rečenica u gornjem primjeru može se prepisati na sljedeći način:
Takve alternativni način specificiranje stupaca po njihovoj poziciji umjesto imena koristi se ako kriterij naručivanja sadrži agregatnu funkciju. (Drugi način je da koristite nazive stupaca, koji se tada pojavljuju u ORDER BY klauzuli.) Međutim, u ORDER BY klauzuli, preporučuje se da se stupci specificiraju svojim imenima, a ne brojevima, kako bi se olakšalo ažuriranje pitajte da li kolone treba dodati ili ukloniti sa liste odabira. Određivanje stupaca u ORDER BY klauzuli njihovim brojevima prikazano je u primjeru ispod:
USE SampleDb; SELECT ProjectNumber, COUNT(*) "Broj zaposlenih" FROM Works_on GROUP BY ProjectNumber ORDER BY 2 DESC;
Ovdje se za svaki projekat odabire broj projekta i broj zaposlenih koji u njemu učestvuju, sortirajući rezultat u opadajućem redoslijedu prema broju zaposlenih.
Transact-SQL stavlja NULL vrijednosti na početak liste kada se sortira u rastućem redoslijedu, a na kraj liste kada se sortira u opadajućem redoslijedu.
Korištenje ORDER BY klauzule za paginiranje rezultata
Prikazivanje rezultata upita na trenutnoj stranici može se implementirati u korisnička aplikacija, ili naložite poslužitelju baze podataka da to učini. U prvom slučaju, svi redovi baze podataka se šalju aplikaciji, čiji je zadatak da odabere tražene redove i prikaže ih. U drugom slučaju, samo redovi potrebni za trenutnu stranicu se biraju i prikazuju sa strane servera. Kao što biste mogli očekivati, stranica na strani servera obično pruža bolje performanse pošto klijentu se šalju samo redovi potrebni za prikaz.
Da bi podržao kreiranje stranice na strani servera, SQL Server 2012 uvodi dvije nove klauzule SELECT izraza: OFFSET i FETCH. Primjena ove dvije rečenice prikazana je u primjeru ispod. Ovdje se iz AdventureWorks2012 baze podataka (koju možete pronaći u izvorima) preuzimaju poslovni ID, naziv radnog mjesta i rođendan svih zaposlenica, sortirajući rezultat po nazivu posla uzlaznim redoslijedom. Rezultirajući skup redova se dijeli na stranice od 10 redova i prikazuje se treća stranica:
U rečenici OFFSET specificira broj redova rezultata koje treba preskočiti u prikazanom rezultatu. Ovaj broj se izračunava nakon što se redovi sortiraju prema ORDER BY klauzuli. U rečenici DETCH NEXT označava broj zadovoljavajućih WHERE klauzula i sortirane redove koje treba vratiti. Parametar ove klauzule može biti konstanta, izraz ili rezultat drugog upita. Klauzula FETCH NEXT je slična klauzuli PETCH FETCH.
Glavni cilj pri kreiranju stranica na strani servera je biti u mogućnosti implementirati uobičajene forme stranica koristeći varijable. Ovaj zadatak se može izvršiti kroz SQL paket Server.
SELECT izraz i svojstvo IDENTITY
IDENTITY svojstvo omogućava vam da definirate vrijednosti za određenu kolonu tabele kao brojač koji se automatski povećava. Stupci numeričkog tipa podataka kao što su TINYINT, SMALLINT, INT i BIGINT mogu imati ovo svojstvo. Za takvu kolonu tabele, Database Engine automatski generiše sekvencijalne vrednosti počevši od navedene početne vrednosti. Dakle, svojstvo IDENTITY se može koristiti za generiranje jednocifrenih numeričkih vrijednosti za odabranu kolonu.
Tablica može sadržavati samo jedan stupac sa svojstvom IDENTITY. Vlasnik tabele ima mogućnost da odredi početnu vrednost i inkrement, kao što je prikazano u donjem primeru:
USE SampleDb; KREIRAJ TABELU Proizvod (Id INT IDENTITY(10000, 1) NOT NULL, Ime NVARCHAR(30) NOT NULL, Cijena NOVAC) UMETNI U PROIZVOD(Naziv, Cijena) VRIJEDNOSTI ("Item1", 10), ("Item2", 15) , ("Item3", 8), ("Item4", 15), ("Item5", 40); -- Vraća 10004 SELECT IDENTITYCOL FROM Product WHERE Naziv = "Proizvod5"; -- Slično prethodnom izrazu SELECT $identity FROM Product WHERE Ime = "Proizvod5";
Ovaj primjer prvo kreira tablicu proizvoda koja sadrži stupac Id sa svojstvom IDENTITY. Vrijednosti u koloni Id sistem automatski generiše, počevši od 10.000 i povećavajući se za jedan za svaku narednu vrijednost: 10.000, 10.001, 10.002 itd.
Nekoliko sistemskih funkcija i varijabli pridruženo je svojstvu IDENTITY. Na primjer, primjer koda koristi $identity sistemska varijabla. Kao što možete vidjeti iz izlaza ovog koda, ova varijabla automatski referencira svojstvo IDENTITY. Možete koristiti i umjesto toga funkcija sistema IDENTITYCOL.
Početna vrijednost i prirast stupca sa svojstvom IDENTITY mogu se pronaći pomoću funkcija IDENT_SEED i IDENT_INCR respektivno. Ove funkcije se primjenjuju na sljedeći način:
USE SampleDb; SELECT IDENT_SEED("Proizvod"), IDENT_INCR("Proizvod")
Kao što je već spomenuto, IDENTITY vrijednosti postavlja sistem automatski. Ali korisnik može eksplicitno odrediti svoje vrijednosti za određene redove postavljanjem parametra IDENTITY_INSERT ON prije umetanja eksplicitne vrijednosti:
SET IDENTITY INSERT ime tablice ON
Budući da se opcija IDENTITY_INSERT može koristiti za postavljanje bilo koje vrijednosti za stupac svojstva IDENTITY, uključujući duplikat vrijednosti, svojstvo IDENTITY obično ne provodi jedinstvenost vrijednosti stupca. Stoga, ograničenja UNIQUE ili PRIMARY KEY trebaju se koristiti za provođenje jedinstvenosti vrijednosti stupca.
Prilikom umetanja vrijednosti u tabelu nakon što je IDENTITY_INSERT postavljen na uključeno, sistem kreira sljedeću vrijednost stupca IDENTITY povećavajući najveću trenutnu vrijednost te kolone.
CREATE SEQUENCE izraz
Upotreba svojstva IDENTITY ima nekoliko značajnih nedostataka, od kojih su najznačajniji sljedeći:
aplikacija svojstva je ograničena na navedenu tabelu;
nova vrijednost kolone se ne može dobiti ni na koji drugi način osim njenom primjenom;
svojstvo IDENTITY se može specificirati samo prilikom kreiranja stupca.
Iz ovih razloga, SQL Server 2012 uvodi sekvence koje imaju istu semantiku kao svojstvo IDENTITY, ali bez prethodno navedenih nedostataka. U ovom kontekstu, sekvenca je funkcionalnost baze podataka koja vam omogućava da odredite vrijednosti brojača za različite objekte baze podataka, kao što su stupci i varijable.
Sekvence se kreiraju pomoću instrukcije CREATE SEQUEENCE. Naredba CREATE SEQUENCE je definirana u SQL standard i podržan je od drugih sistema relacijskih baza podataka kao što su IBM DB2 i Oracle.
Primjer ispod pokazuje kako kreirati sekvencu u SQL Serveru:
USE SampleDb; KREIRAJ SEQUENCE dbo.Sequence1 KAO INT POČINJE SA 1 POVEĆANJEM ZA 5 MINVRIJEDNOSTI 1 MAKSIMALNA VRIJEDNOST 256 CIKLUS;
U gornjem primjeru, vrijednosti Sequence1 se automatski generišu od strane sistema, počevši od vrijednosti 1 sa prirastom od 5 za svaku narednu vrijednost. Dakle, u ponuda START početna vrijednost je navedena, i INCREMENT ponuda- korak. (Korak može biti pozitivan ili negativan.)
U sljedeće dvije fakultativne rečenice MINVRIJEDNOST i MAXVALUE navedene su minimalne i maksimalne vrijednosti objekta sekvence. (Imajte na umu da MINVALUE mora biti manji ili jednak početna vrijednost, i MAXVALUE ne mogu biti veći od gornje granice tipa podataka specificiranog za sekvencu.) U klauzuli CIKLUS označava da se sekvenca ponavlja od početka kada se prekorači maksimalna (ili minimalna za sekvencu sa negativnim korakom) vrijednost. Po defaultu, ova klauzula je NO CYCLE, što znači da prekoračenje maksimalne ili minimalne vrijednosti sekvence uzrokuje izuzetak.
Glavna karakteristika sekvenci je njihova nezavisnost od tabela, tj. mogu se koristiti sa bilo kojim objektom baze podataka kao što su stupci tablice ili varijable. (Ovo svojstvo ima pozitivan učinak na pohranu, a samim tim i na performanse. Određeni niz ne mora biti pohranjen; pohranjuje se samo njegova posljednja vrijednost.)
Nove vrijednosti sekvence se kreiraju sa SLJEDEĆA VRIJEDNOST ZA izraze, čija je upotreba prikazana u primjeru ispod:
USE SampleDb; -- Vraća 1 SELECT SLJEDEĆU VRIJEDNOST ZA dbo.sequence1; -- Vraća 6 (sljedeći korak) SELECT SLEDEĆU VRIJEDNOST ZA dbo.sequence1;
Možete koristiti izraz SLJEDEĆA VRIJEDNOST FOR da dodijelite rezultat niza varijabli ili ćeliji stupca. Donji primjer pokazuje upotrebu ovog izraza za dodjelu rezultata koloni:
USE 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 kreira tablicu proizvoda sa četiri stupca. Zatim, dva INSERT izraza ubacuju dva reda u ovu tabelu. Prve dvije ćelije u prvoj koloni imat će vrijednosti 11 i 16.
Primjer ispod pokazuje korištenje kataloškog pogleda sys.sequences da vidite trenutnu vrijednost sekvence bez korištenja:
Obično se izraz NEXT VALUE FOR koristi u INSERT izrazu da prisili sistem da ubaci generirane vrijednosti. Ovaj izraz se također može koristiti kao dio upita u više redova pomoću klauzule OVER.
Za promjenu svojstva postojeće sekvence, koristite ALTER SEQUENCE izraz. Jedna od najvažnijih upotreba ove izjave je opcija RESTART WITH, koja resetira navedeni niz. Sljedeći primjer pokazuje upotrebu naredbe ALTER SEQUENCE za resetiranje gotovo svih svojstava Sequence1:
USE SampleDb; ALTER SEQUENCE dbo.sequence1 RESTART SA 100 POVEĆANJA ZA 50 MIN VRIJEDNOSTI 50 MAKSIMALNE VRIJEDNOSTI 200 BEZ CIKLUSA;
Izbrišite sekvencu koristeći instrukciju DROP SEQUENCE.
Postavite operatore
Pored operatora o kojima smo ranije govorili, Transact-SQL podržava još tri operatora skupa: UNION, INTERSECT i EXCEPT.
UNION operater
UNION operater kombinuje rezultate dva ili više upita u jedan skup rezultata koji uključuje sve redove koji pripadaju svim upitima u spoju. Shodno tome, rezultat spajanja dvije tabele je nova tabela koja sadrži sve redove uključene u jednu od originalnih tabela ili obe ove tabele.
Opšti oblik UNION operatora izgleda ovako:
select_1 UNION select_2(select_3])...
Opcije select_1, select_2, ... su SELECT izjave koje kreiraju spoj. Ako se koristi opcija SVE, prikazuju se svi redovi, uključujući duplikate. U operatoru UNION, parametar ALL ima isto značenje kao u SELECT listi za odabir, s jednom razlikom: za SELECT listu za odabir, ovaj parametar se primjenjuje prema zadanim postavkama, ali za UNION operator mora biti eksplicitno specificiran.
U svom izvornom obliku, SampleDb baza podataka nije prikladna za demonstraciju upotrebe UNION operatora. Stoga, ovaj odjeljak kreira novu tabelu, EmployeeEnh, koja je identična postojeća tabela Zaposlenik, ali ima dodatnu kolonu Grad. Ova kolona pokazuje gdje zaposleni žive.
Kreiranje tabele EmployeeEnh pruža nam priliku da demonstriramo upotrebu klauzule INTO u SELECT izrazu. Naredba SELECT INTO izvodi dvije operacije. Prvo, kreira se nova tabela sa stupcima navedenim na listi za odabir SELECT. Zatim se ubacuju redovi originalne tabele novi sto. Ime nove tablice je navedeno u INTO klauzuli, a ime izvorne tablice navedeno je u klauzuli FROM.
Primjer ispod prikazuje kreiranje tabele EmployeeEnh iz tabele Employee:
USE SampleDb; SELECT * INTO EmployeeEnh FROM Employee; ALTER TABLE EmployeeEnh DODAJ Grad NCHAR(40) NULL;
U ovom primjeru, naredba SELECT INTO kreira tablicu EmployeeEnh, u nju umeće sve redove iz izvorne tablice Employee, a zatim naredba ALTER TABLE dodaje stupac City u novu tablicu. Ali dodana kolona Grad ne sadrži nikakve vrijednosti. Vrijednosti u ovoj koloni mogu se umetnuti kroz okruženje menadžment studio ili sa sljedećim kodom:
USE SampleDb; UPDATE EmployeeEnh SET City="Kazan" WHERE Id=2581; UPDATE EmployeeEnh SET City = "Moskva" GDJE Id = 9031; UPDATE EmployeeEnh SET Grad = "Jekaterinburg" GDJE Id = 10102; UPDATE EmployeeEnh SET Grad = "Sankt Peterburg" GDJE Id = 18316; UPDATE EmployeeEnh SET Grad = "Krasnodar" GDJE Id = 25348; UPDATE EmployeeEnh SET City="Kazan" GDJE Id=28559; UPDATE EmployeeEnh SET City="Perm" WHERE Id=29346;
Sada smo spremni da demonstriramo upotrebu izjave UNIJE. Primjer u nastavku prikazuje upit za kreiranje spajanja između EmployeeEnh i Department tablica koristeći ovu naredbu:
USE SampleDb; ODABERITE Grad KAO "Grad" IZ UNIONA EmployeeEnh ODABIRITE lokaciju IZ Odjela;
Rezultat ovog upita je:
Samo kompatibilne tablice mogu se spojiti pomoću UNION izraza. Pod kompatibilnim tabelama podrazumijevamo da obje liste kolona u selekciji moraju sadržavati isti broj kolona i da odgovarajuće kolone moraju imati kompatibilne tipove podataka. (U smislu kompatibilnosti, tipovi podataka INT i SMALLINT nisu kompatibilni.)
Rezultat spajanja se može poredati samo pomoću ORDER BY klauzule u posljednja instrukcija SELECT kao što je prikazano u primjeru ispod. GROUP ponude BY i HAVING se mogu koristiti sa odvojena uputstva SELECT, ali ne u samom spoju.
Upit u ovom primjeru dohvaća zaposlenike koji ili rade u odjelu d1 ili su počeli raditi na projektu prije 1. januara 2008.
UNION operator podržava opciju SVE. Kada se koristi ova opcija, duplikati se ne uklanjaju iz skupa rezultata. Možete koristiti OR operator 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 SELECT izraza je zamijenjen jednim SELECT izrazom sa skupom OR naredbi.
INTERSECT i EXCEPT iskazi
Dva druga operatera za rad sa setovima, INTERSECT i OSIM, definiraju sjecište i razliku, respektivno. Ispod presjeka u ovom kontekstu je skup redova koji pripadaju objema tablicama. A razlika između dvije tablice definira se kao sve vrijednosti koje pripadaju prvoj tablici, a nisu prisutne u drugoj. Donji primjer pokazuje upotrebu INTERSECT izraza:
Transact-SQL ne podržava upotrebu opcije ALL s naredbom INTERSECT ili EXCEPT. Upotreba izraza EXCEPT prikazana je u primjeru ispod:
Imajte na umu da ova tri operatora skupa imaju različit prioritet izvršenja: operator INTERSECT ima najveći prioritet, a slijedi ga EXCEPT izjava, a UNION operator ima najmanji prioritet. Nepažnja prema prioritetu izvršenja kada se koristi više različitih operatora skupa može dovesti do neočekivanih rezultata.
CASE izrazi
U oblasti aplikativnog programiranja baze podataka, ponekad je potrebno modificirati prezentaciju podataka. Na primjer, ljudi se mogu podijeliti tako što ih kodiraju prema njihovoj društvenoj pripadnosti, koristeći vrijednosti 1, 2 i 3, koje označavaju muškarce, žene, odnosno djecu. Ova tehnika programiranja može smanjiti vrijeme potrebno za implementaciju programa. CASE izraz Transact-SQL jezik olakšava implementaciju ovog tipa kodiranja.
Za razliku od većine programskih jezika, CASE nije izjava, već izraz. Stoga se izraz CASE može koristiti gotovo svuda gdje Transact-SQL jezik dozvoljava upotrebu izraza. Izraz CASE ima dva oblika:
jednostavan CASE izraz;
izraz za pretragu CASE.
Sintaksa za jednostavan CASE izraz je sljedeća:
Naredba s jednostavnim CASE izrazom prvo pretražuje listu svih izraza u WHEN klauzula prvi izraz koji odgovara izrazu_1, a zatim izvršava odgovarajući THEN klauzula. Ako na listi WHEN ne postoji odgovarajući izraz, onda ELSE klauzula.
Sintaksa za CASE izraz za pretraživanje je:
V ovaj slučaj traži se prvi podudarni uslov, a zatim se izvršava odgovarajuća klauzula THEN. Ako nijedan od uslova ne odgovara zahtjevima, ELSE klauzula se izvršava. Upotreba izraza za pretragu CASE prikazana je u primjeru ispod:
USE SampleDb; SELECT ProjectName, CASE WHEN Budžet > 0 I Budžet 100000 I Budžet 150000 I Budžet
Rezultat ovog upita je:
Ovaj primjer ponderira budžete svih projekata i prikazuje njihove izračunate težine zajedno sa odgovarajućim nazivima projekata.
Primjer ispod pokazuje drugi način korištenja izraza CASE, gdje klauzula WHEN sadrži potupite koji su dio izraza:
USE SampleDb; SELECT ProjectName, CASE WHEN p1.Budget (IZABERITE AVG(p2.Budget) IZ Projekta p2) ONDA "Iznad proseka" KRAJ "Kategorija budžeta" IZ Projekta p1;
Rezultat ovog upita je sljedeći:
Korištenje agregatnih funkcija
SQL definira mnoge ugrađene funkcije različitih kategorija, među kojima posebno mjesto zauzimaju agregatne funkcije koje rade na vrijednostima stupaca više redova i vraćaju jednu vrijednost. Argumenti agregatnih funkcija mogu biti i kolone tabela i rezultati izraza nad njima. Agregatne funkcije mogu same biti uključene u druge aritmetičke izraze. Sljedeća tabela navodi najčešće korištene standardne unarne agregatne funkcije.
Opći format unarne agregatne funkcije je sljedeći:
function_name([SVE | DISTINCT] izraz)
gdje DISTINCT specificira da funkcija treba samo uzeti u obzir razna značenja argument i SVE - sve vrijednosti, uključujući duplikate (ova opcija se koristi po defaultu). Na primjer, funkcija AVG sa ključnom riječi DISTINCT za redove stupaca sa vrijednostima 1, 1, 1 i 3 će vratiti 2, ali s ključnom riječi ALL vraća 1,5.
Agregatne funkcije se koriste u klauzulama SELECT i HAVING. Ovdje ćemo pogledati njihovu upotrebu u klauzuli SELECT. U ovom slučaju, izraz u argumentu funkcije primjenjuje se na sve redove ulazne tablice klauzule SELECT. Osim toga, ne možete koristiti i agregatne funkcije i stupce tablice (ili izraze s njima) u SELECT klauzuli u odsustvu GROUP BY klauzule, o čemu ćemo raspravljati u sljedeći odjeljak.
Funkcija COUNT ima dva formata. U prvom slučaju vraća se broj redova ulazne tablice, u drugom slučaju broj vrijednosti argumenata u ulaznoj tablici:
- COUNT(*)
- COUNT( izraz)
Najjednostavniji način korištenja ove funkcije je prebrojavanje broja redova u tablici (bilo svih ili onih koji ispunjavaju dati uvjet). Za to se koristi prva varijanta sintakse.
Upit: Broj vrsta proizvoda, informacije o kojima su dostupne u bazi podataka.
SELECT COUNT(*) AS "Broj proizvoda"
IZ proizvoda
U drugoj varijanti sintakse funkcije COUNT, ime jedne kolone može se koristiti kao argument. U ovom slučaju, broji se ili sve vrijednosti u ovoj koloni ulazne tablice ili samo one koje se ne ponavljaju (kada se koristi ključna riječ DISTINCT).
Upit: Broj različitih imena sadržanih u tabeli Kupci.
ODABIR BROJ (DISTINCT FNAME)
OD kupca
Upotreba ostalih unarnih agregatnih funkcija je slična COUNT, osim što za funkcije MIN i MAX upotreba ključnih riječi DISTINCT i ALL nema smisla. Uz funkcije COUNT, MAX i MIN, pored numeričkih polja mogu se koristiti i znakovna polja. Ako argument agregatne funkcije ne sadrži nikakve vrijednosti, funkcija COUNT vraća 0, a sve ostale vraćaju NULL.
SELECT MAX(OrdDate)
OD
WHERE OrdDate"1.09.2010"
Zadatak za samostalan rad: Formulirajte na jeziku SQL upiti uzorkovati sljedeće podatke:
- Ukupni trošak svih narudžbi;
- Broj različitih gradova sadržanih u tabeli kupaca.
