GRUPI PËR klauzolë(Deklarata SELECT) ju lejon të gruponi të dhënat (rreshtat) sipas vlerës së një kolone ose kolonave ose shprehjeve të shumta. Rezultati do të jetë një grup rreshtash përmbledhës.
Secila kolonë në listën e përzgjedhur duhet të jetë e pranishme në klauzolën GROUP BY, përjashtimet e vetme janë konstantet dhe kolonat - operandët e funksioneve agregate.
Një tabelë mund të grupohet sipas çdo kombinimi të kolonave të saj.
Funksionet agregate përdoren për të marrë një vlerë totale të vetme nga një grup rreshtash. Të gjithë funksionet agregate kryejnë llogaritjet në një argument të vetëm, i cili mund të jetë ose një kolonë ose një shprehje. Çdo funksion agregat vlerësohet në një vlerë konstante që shfaqet në një kolonë të veçantë në rezultat.
Funksionet e përmbledhura janë të specifikuara në listën e kolonave të një deklarate SELECT, e cila mund të përmbajë gjithashtu një klauzolë GROUP BY. Nëse deklarata SELECT nuk përmban një klauzolë GROUP BY, dhe lista e kolonave të përzgjedhjes përmban të paktën një funksion agregat, atëherë ajo nuk duhet të përmbajë kolona të thjeshta. Nga ana tjetër, lista e kolonave zgjidhni mund të përmbajë emra kolonash që nuk janë argumente për funksionin agregat nëse ato kolona përdoren si argumente për klauzolën GROUP BY.
Nëse pyetja përmban një klauzolë WHERE, atëherë funksionet agregate llogaritin një vlerë për rezultatet e përzgjedhjes.
Funksionet agregate MIN dhe MAX llogariten respektivisht vlerat më të vogla dhe më të mëdha të kolonës. Argumentet mund të jenë numra, vargje dhe data. Të gjitha vlerat NULL hiqen para llogaritjes (d.m.th., nuk merren parasysh).
Funksioni agregat SUM llogarit shumën totale të vlerave të kolonës. Vetëm numrat mund të jenë argumente. Përdorimi i parametrit DISTINCT heq çdo vlerë të kopjuar në kolonë përpara se të aplikoni funksionin SUM. Po kështu, hiqni të gjitha vlerat NULL përpara se të aplikoni këtë funksion agregat.
Funksioni agregat AVG kthen mesataren e të gjitha vlerave në një kolonë. Argumentet gjithashtu mund të jenë vetëm numra, dhe të gjitha vlerat NULL hiqen para vlerësimit.
Funksioni i përmbledhur COUNT ka dy forma të ndryshme:
- COUNT (col_name) - numëron numrin e vlerave në kolonën col_name, vlerat NULL nuk numërohen
- COUNT (*) - numëron numrin e rreshtave në tabelë, gjithashtu merren parasysh vlerat NULL
Nëse pyetja përdor fjalën kyçe DISTINCT, të gjitha vlerat e dyfishta të kolonës hiqen përpara se të përdoret funksioni COUNT.
COUNT_BIG funksion i njëjti funksion si COUNT. Dallimi i vetëm midis të dyjave është në llojin e rezultatit që ata japin: COUNT_BIG kthen gjithmonë vlera BIGINT, ndërsa COUNT kthen vlera të dhënash INTEGER.
V Duke pasur ofertë përcakton një kusht që zbatohet në një grup rreshtash. Ka të njëjtin kuptim për grupet e rreshtave si klauzola WHERE për përmbajtjen e tabelës përkatëse (WHERE zbatohet para grupimit, HAVING pas).
Mësimi do të mbulojë temën sql riemërimi i një kolone (fushash) duke përdorur fjalën e funksionit AS; trajtoi gjithashtu temën e funksioneve agregate në sql. Do të analizohen shembuj specifikë të kërkesave
Ju mund të riemërtoni emrat e kolonave në pyetje. Kjo i bën rezultatet më të lexueshme.
Në SQL, riemërtimi i fushave shoqërohet me përdorimin e fjalë kyçe AS e cila përdoret për të riemërtuar emrat e fushave në grupet e rezultateve
Sintaksë:
ZGJIDH<имя поля>AS<псевдоним>NGA...
Le të shohim një shembull të riemërtimit në SQL:
Shembull DB "Instituti": Shfaqni emrat e mësuesve dhe pagat e tyre, për ata mësues pagat e të cilëve janë nën 15000, riemërtoni fushën zarplata në "Paga e ulët"
✍ Zgjidhja:
Riemërtimi i kolonave në SQL është shpesh i nevojshëm kur llogaritni vlerat e lidhura me fusha të shumta tabelat. Le të shqyrtojmë një shembull:
Shembull DB "Instituti": Nga tabela e mësuesve, nxirrni fushën e emrit dhe llogaritni shumën e pagës dhe bonusit, duke emërtuar fushën "Shpërblim paga"
✍ Zgjidhja:
| 1 2 | SELECT emrin, (zarplata + premia) AS zarplata_premia FROM mësues; |
SELECT emrin, (zarplata + premia) AS zarplata_premia FROM mësues;
Rezultati:
Funksionet Agregate në SQL
Funksionet agregate në sql përdoren për të marrë totale dhe për të llogaritur shprehjet:
Të gjitha funksionet agregate kthejnë një vlerë të vetme.
Funksionet COUNT, MIN dhe MAX zbatohen për çdo lloj të dhënash.
Funksionet SUM dhe AVG përdoren vetëm për fushat numerike.
Ekziston një ndryshim midis funksioneve COUNT (*) dhe COUNT (): i dyti nuk merr parasysh vlerat NULL kur numëron.
E rëndësishme: kur punoni me funksione agregate në SQL, përdoret një fjalë e veçantë AS
Shembull DB "Instituti": Merrni vlerën e pagës më të lartë mes mësuesve, shfaqni rezultatin si "Max_zp"
✍ Zgjidhja:
| SELECT MAX (zarplata) AS max_zp NGA mësuesit; |
SELECT MAX (zarplata) AS max_zp NGA mësuesit;
Rezultatet:
Le të shqyrtojmë një shembull më kompleks të përdorimit të funksioneve agregate në sql.
✍ Zgjidhja:
Klauzola GROUP BY në SQL
Grupi sipas klauzolës në sql zakonisht përdoret në lidhje me funksionet agregate.
Funksionet e përgjithshme kryhen në të gjitha rreshtat e pyetjeve që rezultojnë. Nëse pyetja përmban një klauzolë GROUP BY, çdo grup rreshtash i specifikuar në klauzolën GROUP BY përbën një grup dhe funksionet e përgjithshme ekzekutohen. për secilin grup veç e veç.
Shqyrtoni një shembull me tabelën e mësimeve: 
Shembull:
E rëndësishme: Kështu, si rezultat i përdorimit të GROUP BY, të gjitha rreshtat dalëse të pyetjes ndahen në grupe të karakterizuara nga të njëjtat kombinime vlerash në këto kolona (d.m.th., funksionet agregate kryhen për secilin grup veç e veç).
Duhet të kihet parasysh se kur grupohen nga një fushë që përmban vlera NULL, të gjitha regjistrimet e tilla do të bien në një grup.
Për lloje të ndryshme printerësh, përcaktoni koston dhe sasinë mesatare të tyre (d.m.th. veçmas për lazer, inkjet dhe matricë). Përdorni funksionet agregate. Rezultati duhet të duket si ky:
Duke pasur deklaratë SQL
Klauzola HAVING në SQL nevojitet për të vërtetuar vlerat, të cilat fitohen duke përdorur funksionin agregat pas grupimit(pas përdorimit GROUP BY). Një kontroll i tillë nuk mund të përfshihet në një klauzolë WHERE.
Shembull: Dyqan kompjuterësh DB... Llogaritni çmimin mesatar të kompjuterëve me të njëjtën shpejtësi procesori. Llogaritni vetëm për ato grupe çmimi mesatar i të cilëve është më pak se 30,000.
- Funksionet agregate përdoren si emrat e fushave në deklaratën SELECT, me një përjashtim: ata marrin emrin e fushës si argument. Me funksione SHUMË dhe AVG mund të përdoren vetëm fushat numerike. Me funksione COUNT, MAX dhe MIN mund të përdoren si fushat numerike ashtu edhe ato të karaktereve. Kur përdoret me fushat e karaktereve MAX dhe MIN do t'i përkthejë në ekuivalentin ASCII dhe do t'i përpunojë sipas rendit alfabetik. Disa DBMS lejojnë përdorimin e agregateve të mbivendosur, por ky është një devijim nga standardi ANSI me të gjitha pasojat që pasojnë.
Për shembull, mund të llogarisni numrin e studentëve që kanë kaluar provimet në secilën disiplinë. Për ta bërë këtë, duhet të ekzekutoni një pyetje të grupuar sipas fushës "Disiplina" dhe të shfaqni si rezultat emrin e disiplinës dhe numrin e rreshtave në grup për këtë disiplinë. Përdorimi i karakterit * si argument për funksionin COUNT do të thotë që të gjitha rreshtat në grup janë të numëruara.
ZGJIDH R1. Disiplinë, COUNT (*)
GRUP NGA R1 Disiplina;
Rezultati:
SELECT R1 Disiplinë, COUNT (*)
KU R1. NUK ËSHTË rezultati NULL
GRUP NGA R1 Disiplina;
Rezultati:
nuk do të përfshihet në grupin e tupave përpara grupimit, kështu që numri i tupave në grup për disiplinën "Teoria e Informacionit" do të jetë 1 më pak.
Një rezultat i ngjashëm mund të merret nëse e shkruani kërkesën në mënyrën e mëposhtme:
ZGJIDH R1. Disiplinë, COUNT (R1. Vlerësim)
GRUPI NGA R1. Disipline;
Funksioni COUNT (EMRI ATRIBUTE) numëron numrin e vlerave të përcaktuara në një grup, në krahasim me një funksion COUNT (*), i cili numëron numrin e rreshtave në grup. Në të vërtetë, në grupin me disiplinën "Teoria e informacionit" do të ketë 4 rreshta, por vetëm 3 vlera specifike të atributit "Vlerësimi".
Rregullat për trajtimin e vlerave null në funksionet agregate
Nëse ndonjë vlerë në kolonë është e barabartë I PAVLEFSHËM ato përjashtohen gjatë llogaritjes së rezultatit të funksionit.
Nëse të gjitha vlerat në një kolonë janë të barabarta I PAVLEFSHËM, pastaj Shuma maksimale minimale mesatare = NULL, numërim = 0 (zero).
Nëse tabela është bosh, numërimi (*) = 0 .
Funksionet agregate mund të përdoren edhe pa operacionin paraprak të grupimit, në këtë rast e gjithë relacioni konsiderohet si një grup dhe për këtë grup mund të llogaritet një vlerë për grup.
Rregulla për interpretimin e funksioneve agregate
Funksionet agregate mund të përfshihen në listën e daljes dhe më pas ato aplikohen në të gjithë tabelën.
SELECT MAX (Rezultati) nga R1 do të japë vlerësimin maksimal në seancë;
ZGJIDH SHUMEN nga R1 do të japë shumën e të gjitha vlerësimeve për sesion;
SELECT AVG (Rezultati) nga R1 do të japë një rezultat mesatar për të gjithë seancën.
2; Rezultati: "width = 640" Duke iu referuar sërish bazës së të dhënave Session (tabelat R1), do të gjejmë numrin e provimeve të mbartura me sukses:
ZGJIDH NUMRIN (*) Si Me qira _ provimet
KU Klasa 2;
Rezultati:
Argumenti i funksioneve agregate mund të jetë kolona individuale të tabelave. Për të llogaritur, për shembull, numrin e vlerave të dallueshme të një kolone të caktuar në një grup, është e nevojshme të përdorni fjalën kyçe DISTINCT së bashku me emrin e kolonës. Le të llogarisim numrin e notave të ndryshme të marra për secilën disiplinë:
ZGJIDH Disiplinë R1, COUNT (Vlerësim DISTINCT R1)
KU R1. NUK ËSHTË rezultati NULL
GRUP NGA R1 Disiplina;
Rezultati:
I njëjti rezultat arrihet nëse përjashtoni kushtin eksplicit në pjesën WHERE, në të cilin rast pyetja do të duket kështu:
ZGJIDH R1. Disiplinë, COUNT (DISTINCT R1. Vlerësim)
GRUPI NGA R1. Disipline;
Funksioni COUNT (DISTINCT R1. Vlerësim) konsideron vetëm të sigurt të ndryshme vlerat.
Në mënyrë që të merret rezultati i dëshiruar në këtë rast, është e nevojshme të bëhet një transformim paraprak i llojit të të dhënave të kolonës "Score", duke e sjellë atë në një lloj real, atëherë rezultati i llogaritjes së mesatares nuk do të jetë një numër i plotë. Në këtë rast, kërkesa do të duket si kjo:
2 Grupi sipas R2. Grupi, R1. Disipline; Këtu, funksioni CAST () konverton kolonën "Score" në një lloj të vlefshëm të dhënash. "gjerësia = 640" Zgjidhni R2.Group, R1.Discipline, Numëroni (*) si Total, AVG (cast (Rezultati si dhjetor (3,1))) si Mesatarja_pikë
Nga R1, R2
ku R1. Emri i plotë = R2. Emri dhe R1. rezultati nuk është nul
dhe R1. Klasa 2
Grupi sipas R2. Grupi, R1. Disipline;
Këtu funksioni CAST () konverton kolonën Score në një lloj të vlefshëm të dhënash.
Ju nuk mund të përdorni funksione agregate në klauzolën WHERE sepse kushtet në këtë seksion vlerësohen në terma të një rreshti të vetëm dhe funksionet agregate vlerësohen në terma të grupeve të rreshtave.
Klauzola GROUP BY ju lejon të përcaktoni një nëngrup vlerash në një fushë të caktuar në terma të një fushe tjetër dhe të aplikoni një funksion agregat në nëngrup. Kjo bën të mundur kombinimin e fushave dhe funksioneve të përmbledhura në një klauzolë të vetme SELECT. Funksionet agregate mund të përdoren si në shprehjen për nxjerrjen e rezultateve të rreshtit SELECT, ashtu edhe në shprehjen për përpunimin e grupeve të krijuara HAVING. Në këtë rast, çdo funksion agregat llogaritet për çdo grup të përzgjedhur. Vlerat e marra gjatë llogaritjes së funksioneve agregate mund të përdoren për të shfaqur rezultatet përkatëse ose për gjendjen e përzgjedhjes së grupeve.
Le të ndërtojmë një pyetje që shfaq grupet në të cilat janë marrë më shumë se një deuce në një disiplinë në provime:
një; Rezultati: "width = 640" ZGJIDH R2. Grupi
NGA R1, R2
KU R1. Emri i plotë = R2. Emri DHE
Rezultati R1 = 2
GRUPI NGA R2.Grupi,R1.Disiplina
Duke numëruar (*) 1;
Rezultati:
Ne kemi një DB "Bankë", e përbërë nga një tabelë F, e cila ruan relacionin F, që përmban informacione për llogaritë në degët e një banke të caktuar:
Gjeni gjendjen totale të llogarisë në degë. Është e mundur të bëhet një pyetje e veçantë për secilën prej tyre duke zgjedhur SUM nga tabela për secilën degë, por operacioni GROUP BY do t'i vendosë të gjitha në një komandë:
ZGJIDH Dega , SUM ( Pjesa e mbetur )
GRUPI SIPAS Degës;
GRUP NGA zbaton funksionet agregate në mënyrë të pavarur për çdo grup të identifikuar nga vlera e fushës Degë. Grupi përbëhet nga linja me vlerë të njëjtë të fushës Dega, dhe funksioni SHUMË zbatohet veçmas për secilin grup të tillë, pra gjendja totale e llogarisë llogaritet veçmas për secilën degë. Vlera e fushës në të cilën zbatohet GRUP NGA, ka, sipas përkufizimit, vetëm një vlerë për grupin e daljes, ashtu si rezultati i një funksioni agregat.
5000; Argumentet në klauzolën HAVING ndjekin të njëjtat rregulla si në klauzolën SELECT, e cila përdor GROUP BY. Ata duhet të kenë një vlerë për grupin e daljes. "gjerësia = 640" Le të supozojmë se ju zgjidhni vetëm ato degë, vlerat totale të gjendjeve të llogarive të të cilave tejkalojnë 5000 dollarë, si dhe gjendjet totale për degët e zgjedhura. Për të shfaqur degët me gjendje totale mbi 5000 dollarë, duhet të përdorni klauzolën HAVING. Klauzola HAVING përcakton kriteret e përdorura për të hequr grupe specifike nga dalja, ashtu siç bën klauzola WHERE për rreshtat individualë.
Komanda e saktë do të ishte si më poshtë:
SELECT Degën, SUM (Bilanci)
GRUP NGA Dega
duke pasur shumën ( Pjesa e mbetur ) 5 000;
Argumentet në një fjali DUKE respektoni të njëjtat rregulla si në fjali ZGJIDH ku përdoret GRUP NGA... Ata duhet të kenë një vlerë për grupin e daljes.
Komanda e mëposhtme do të ndalohet:
SELECT Degë, SUM (Balanca)
GRUPI SIPAS Degës
HAVING Data e hapjes = 27/12/2004 ;
Fusha Data e hapjes nuk mund të përdoret në një fjali DUKE sepse mund të ketë më shumë se një vlerë për grup dalës. Për të shmangur një situatë të tillë, propozimi DUKE duhet t'i referohet vetëm agregateve dhe fushave të zgjedhura GRUP NGA... Ekziston një mënyrë e saktë për të bërë pyetjen e mësipërme:
SELECT Degë, SUM (Balanca)
WHERE Data e hapjes = '27/12/2004'
GRUPI SIPAS Degës;
Kuptimi i këtij pyetësori është si më poshtë: gjeni shumën e gjendjeve për çdo degë të llogarive të hapura më 27 dhjetor 2004.
Siç u tha më herët, HAVING mund të përdorë vetëm argumente që kanë të njëjtën vlerë për grupin e daljes. Në praktikë, referencat për funksionet agregate janë më të përgjithshmet, por fushat e zgjedhura me GROUP BY janë gjithashtu të vlefshme. Për shembull, ne duam të shohim gjendjen totale të llogarive të degëve në Shën Petersburg, Pskov dhe Uryupinsk:
SELECT Degën, SUM (Bilanci)
NGA F, Q
KU F. Dega = Q. Dega
GRUPI SIPAS Degës
KANË Degë NË (‘Shën Petersburg’, ‘Pskov’, ‘Uryupinsk’);
100,000; Nëse bilanci total është më shumë se 100,000 dollarë, atëherë do ta shohim në raportin që rezulton, përndryshe do të marrim një raport bosh. "gjerësia = 640"
Prandaj, në shprehjet aritmetike të kallëzuesve të përfshirë në kushtin e përzgjedhjes së klauzolës HAVING, mund të përdorni drejtpërdrejt vetëm specifikimet e kolonave të specifikuara si kolona grupimi në klauzolën GROUP BY. Pjesa tjetër e kolonave mund të specifikohet vetëm brenda specifikimeve të funksioneve agregate COUNT, SUM, AVG, MIN dhe MAX, të cilat në këtë rast llogarisin një vlerë agregate për të gjithë grupin e rreshtave. Rezultati i ekzekutimit të klauzolës HAVING është një tabelë e grupuar që përmban vetëm ato grupe rreshtash për të cilat rezultati i vlerësimit të kushtit të përzgjedhjes në pjesën HAVING është TRUE. Në veçanti, nëse klauzola HAVING është e pranishme në një pyetje që nuk përmban një GROUP BY, atëherë rezultati i ekzekutimit të tij do të jetë ose një tabelë bosh, ose rezultat i seksioneve të mëparshme të shprehjes së tabelës, i konsideruar si një grup pa grupimi i kolonave. Le të shohim një shembull. Le të themi se duam të shfaqim shumën totale të bilanceve për të gjitha degët, por vetëm nëse është më shumë se 100,000 dollarë. Në këtë rast, pyetja jonë nuk do të përmbajë operacione grupimi, por do të përmbajë një seksion HAVING dhe do të duket kështu:
ZGJIDH SHUMEN ( Pjesa e mbetur )
duke pasur shumën ( Pjesa e mbetur ) 100 000;
Nëse bilanci total është më shumë se 100,000 dollarë, atëherë do ta shohim në raportin që rezulton, përndryshe do të marrim një raport bosh.
Nënseksionet e mëposhtme përshkruajnë klauzola të tjera SELECT që mund të përdoren në pyetje, si dhe funksione të përgjithshme dhe grupe deklaratash. Më lejoni t'ju kujtoj se deri më tani kemi mbuluar përdorimin e klauzolës WHERE, dhe në këtë artikull do të shikojmë klauzolat GROUP BY, ORDER BY dhe HAVING dhe do të japim disa shembuj të përdorimit të këtyre klauzolave në kombinim me funksionet agregate. që mbështeten në Transact-SQL.
GRUPI PËR klauzolë
Fjali GRUP NGA grupon grupin e rreshtave të zgjedhur për të marrë një grup rreshtash përmbledhës bazuar në vlerat e një ose më shumë kolonave ose shprehjeve. Një rast i thjeshtë përdorimi për klauzolën GROUP BY tregohet në shembullin e mëposhtëm:
PËRDORIMI SampleDb; ZGJIDH PUNËN NGA Punimet_Në GRUPI SIPAS Punës;
Në këtë shembull, pozicionet e punonjësve janë kampionuar dhe grupuar.
Në shembullin e mësipërm, klauzola GROUP BY krijon një grup të veçantë për të gjitha vlerat e mundshme (përfshirë NULL) për kolonën Job.
Përdorimi i kolonave në klauzolën GROUP BY duhet të plotësojë disa kushte. Në veçanti, çdo kolonë në listën e përzgjedhur të pyetjes duhet të shfaqet gjithashtu në klauzolën GROUP BY. Kjo kërkesë nuk zbatohet për konstantat dhe kolonat që janë pjesë e një funksioni agregat. (Funksionet agregate diskutohen në nënseksionin tjetër.) Kjo ka kuptim sepse vetëm kolonave në klauzolën GROUP BY u garantohet një vlerë për grup.
Një tabelë mund të grupohet sipas çdo kombinimi të kolonave të saj. Shembulli i mëposhtëm tregon grupimin e rreshtave në tabelën Works_on nga dy kolona:
PËRDORIMI SampleDb; SELECT Numri i Projektit, Puna NGA Punimet_Në GRUPI SIPAS Numri i projektit, Punë;
Rezultati i kësaj pyetjeje:
Bazuar në rezultatet e pyetjes, mund të shihni se ka nëntë grupe me kombinime të ndryshme të numrit dhe titullit të projektit. Sekuenca e emrave të kolonave në klauzolën GROUP BY nuk duhet të jetë e njëjtë si në listën e kolonave SELECT.
Funksionet agregate
Funksionet agregate përdoren për të marrë vlerat e shumës. Të gjitha funksionet e përgjithshme mund të ndahen në kategoritë e mëposhtme:
funksionet e zakonshme të agregatit;
funksionet e agregatit statistikor;
funksionet e përgjithshme të përcaktuara nga përdoruesi;
funksionet e agregatit analitik.
Këtu do të shikojmë tre llojet e para të funksioneve agregate.
Funksionet e rregullta agregate
Transact-SQL mbështet gjashtë funksionet e mëposhtme agregate: MIN, MAX, SHUMË, AVG, COUNT, COUNT_BIG.
Të gjithë funksionet agregate kryejnë llogaritjet në një argument të vetëm, i cili mund të jetë ose një kolonë ose një shprehje. (Përjashtimi i vetëm është forma e dytë e dy funksioneve, COUNT dhe COUNT_BIG, përkatësisht COUNT (*) dhe COUNT_BIG (*), përkatësisht.) Çdo funksion agregat vlerësohet në një vlerë konstante që shfaqet në një kolonë të veçantë rezultati.
Funksionet e përmbledhura janë të specifikuara në listën e kolonave të një deklarate SELECT, e cila mund të përmbajë gjithashtu një klauzolë GROUP BY. Nëse deklarata SELECT nuk përfshin një klauzolë GROUP BY, dhe lista e kolonave të përzgjedhjes përmban të paktën një funksion agregat, atëherë ajo nuk duhet të përmbajë kolona të thjeshta (përveç kolonave të përdorura si argumente për funksionin agregat). Prandaj, kodi në shembullin e mëposhtëm është i pasaktë:
PËRDORIMI SampleDb; SELECT Mbiemri, MIN (Id) FROM Punonjës;
Këtu, kolona LastName e tabelës Employee nuk duhet të jetë në listën e kolonave të përzgjedhura sepse nuk është një argument për funksionin agregat. Nga ana tjetër, lista e kolonave zgjidhni mund të përmbajë emra kolonash që nuk janë argumente për funksionin agregat nëse ato kolona përdoren si argumente për klauzolën GROUP BY.
Një argument i funksionit agregat mund të paraprihet nga një nga dy fjalë kyçe të mundshme:
TE GJITHATregon që llogaritjet kryhen në të gjitha vlerat në kolonë. Ky është standardi.
TË DAKTUARPërcakton që vetëm vlerat unike të kolonës përdoren për llogaritjet.
Funksionet agregate MIN dhe MAX
Funksionet agregate MIN dhe MAX llogaritin respektivisht vlerat më të vogla dhe më të mëdha në një kolonë. Nëse pyetja përmban një klauzolë WHERE, funksionet MIN dhe MAX kthejnë vlerat më të vogla dhe më të mëdha të rreshtit që përputhen me kushtet e specifikuara. Shembulli i mëposhtëm tregon përdorimin e funksionit të agregatit MIN:
PËRDORIMI SampleDb; - Kthen 2581 SELECT MIN (Id) AS "Minimum Id value" FROM Employee;
Rezultati i dhënë në shembullin e mësipërm nuk është shumë informues. Për shembull, mbiemri i punonjësit që zotëron këtë numër nuk dihet. Por ju nuk mund ta merrni këtë mbiemër në mënyrën e zakonshme, sepse, siç u përmend më herët, nuk mund të specifikoni në mënyrë eksplicite kolonën Mbiemri. Për të marrë mbiemrin e këtij punonjësi së bashku me numrin më të ulët të personelit të punonjësit, përdoret një nënpyetje. Shembulli i mëposhtëm tregon përdorimin e një nënpyetjeje të tillë, ku nënpyetja përmban deklaratën SELECT nga shembulli i mëparshëm:
Rezultati i ekzekutimit të pyetjes:
Përdorimi i funksionit të agregatit MAX tregohet në shembullin e mëposhtëm:
Funksionet MIN dhe MAX gjithashtu mund të pranojnë vargje dhe data si argumente. Në rastin e një argumenti të vargut, vlerat krahasohen duke përdorur rendin aktual të renditjes. Për të gjitha argumentet e të dhënave të përkohshme të tipit të datës, vlera më e ulët e kolonës është data më e hershme dhe vlera më e lartë është më e fundit.
Fjala kyçe DISTINCT mund të përdoret me funksionet MIN dhe MAX. Të gjitha vlerat NULL hiqen nga kolonat e tyre të argumenteve përpara se të përdorni funksionet e përgjithshme MIN dhe MAX.
Funksioni agregat SUM
Agregat Funksioni SUM llogarit shumën totale të vlerave të kolonës. Argumenti për këtë funksion agregat duhet të jetë gjithmonë i një lloji të të dhënave numerike. Përdorimi i funksionit agregat SUM tregohet në shembullin e mëposhtëm:
PËRDORIMI SampleDb; SELECT SUM (Buxhet) "Buxheti total" NGA Projekti;
Ky shembull llogarit buxhetet totale për të gjitha projektet. Rezultati i ekzekutimit të pyetjes:
Në këtë shembull, funksioni i përgjithshëm grupon të gjitha vlerat e buxhetit të projektit dhe përcakton totalin e tyre. Për këtë arsye, pyetja përmban një funksion grupimi të nënkuptuar (si të gjitha pyetjet e ngjashme). Funksioni i grupimit të nënkuptuar nga shembulli i mësipërm mund të specifikohet në mënyrë eksplicite, siç tregohet në shembullin më poshtë:
PËRDORIMI SampleDb; ZGJIDH SHUMËN (Buxhet) "Buxheti total" NGA GRUP E PROJEKTIT NGA ();
Përdorimi i parametrit DISTINCT heq çdo vlerë të kopjuar në kolonë përpara se të aplikoni funksionin SUM. Po kështu, hiqni të gjitha vlerat NULL përpara se të aplikoni këtë funksion agregat.
Funksioni agregat AVG
Agregat Funksioni AVG kthen mesataren aritmetike të të gjitha vlerave në një kolonë. Argumenti për këtë funksion agregat duhet të jetë gjithmonë i një lloji të të dhënave numerike. Të gjitha vlerat NULL hiqen nga argumenti përpara se të zbatohet funksioni AVG.
Përdorimi i funksionit të agregatit AVG tregohet në shembullin e mëposhtëm:
PËRDORIMI SampleDb; - Kthen 133833 SELECT AVG (Buxhet) "Buxheti mesatar për një projekt" FROM Project;
Këtu llogaritet mesatarja aritmetike e buxhetit për të gjitha buxhetet.
Përmbledh funksionet COUNT dhe COUNT_BIG
Agregat COUNT funksion ka dy forma të ndryshme:
COUNT (col_name) COUNT (*)
Forma e parë e funksionit numëron numrin e vlerave në kolonën col_name. Nëse pyetja përdor fjalën kyçe DISTINCT, të gjitha vlerat e dyfishta të kolonës hiqen përpara se të përdoret funksioni COUNT. Kjo formë e funksionit COUNT nuk merr parasysh vlerat NULL kur numëron numrin e vlerave të kolonës.
Përdorimi i formës së parë të funksionit agregat COUNT tregohet në shembullin e mëposhtëm:
PËRDORIMI SampleDb; ZGJIDH numrin e projektit, COUNT (Punë e dallueshme) "Punë në projekt" NGA Works_on GROUP BY Project Number;
Këtu llogaritet numri i pozicioneve të ndryshme për çdo projekt. Rezultati i kësaj pyetjeje:
Siç mund ta shihni nga pyetja në shembull, vlerat NULL nuk u morën parasysh nga funksioni COUNT. (Shuma e të gjitha vlerave në kolonën e punës është 7, jo 11 siç duhet të jetë.)
Forma e dytë e funksionit COUNT, d.m.th. funksioni COUNT (*) numëron numrin e rreshtave në një tabelë. Dhe nëse deklarata SELECT e pyetjes me funksionin COUNT (*) përmban një klauzolë WHERE me një kusht, funksioni kthen numrin e rreshtave që plotësojnë kushtin e specifikuar. Ndryshe nga versioni i parë i funksionit COUNT, forma e dytë nuk i injoron vlerat NULL sepse ky funksion funksionon në vargje, jo në kolona. Shembulli i mëposhtëm tregon përdorimin e funksionit COUNT (*):
PËRDORIMI SampleDb; SELECT Job AS "Lloji i punës", COUNT (*) "Nevojitet punëtorë" FROM Works_on GROUP BY Job;
Këtu llogaritet numri i postimeve në të gjitha projektet. Rezultati i ekzekutimit të pyetjes:
COUNT_BIG funksion i njëjti funksion si COUNT. Dallimi i vetëm midis të dyjave është në llojin e rezultatit që ata japin: COUNT_BIG kthen gjithmonë vlera BIGINT, ndërsa COUNT kthen vlera të dhënash INTEGER.
Funksionet e agregatit statistikor
Funksionet e mëposhtme përbëjnë grupin e funksioneve agregate statistikore:
VARLlogarit variancën statistikore të të gjitha vlerave në një kolonë ose shprehje.
VARPLlogarit variancën statistikore të popullatës së të gjitha vlerave në një kolonë ose shprehje.
STDEVLlogarit devijimin standard (i cili llogaritet si rrënja katrore e variancës përkatëse) të të gjitha vlerave në një kolonë ose shprehje.
STDEVPLlogarit devijimin standard të mbledhjes së të gjitha vlerave në një kolonë ose shprehje.
Funksionet e përgjithshme të përcaktuara nga përdoruesi
Motori i bazës së të dhënave gjithashtu mbështet zbatimin e funksioneve të përcaktuara nga përdoruesi. Kjo aftësi i lejon përdoruesit të plotësojnë funksionet e përgjithshme të sistemit me funksione që ata mund t'i zbatojnë dhe instalojnë vetë. Këto funksione përfaqësojnë një klasë të veçantë funksionesh të përcaktuara nga përdoruesi dhe do të diskutohen në detaje më vonë.
Klauzola e PASJES
Në një fjali DUKE përcakton një kusht që zbatohet në një grup rreshtash. Kështu, kjo klauzolë ka të njëjtin kuptim për grupet e rreshtave si fjalia WHERE për përmbajtjen e tabelës përkatëse. Sintaksa për klauzolën HAVING është si më poshtë:
DUKE gjendje
Këtu parametri i kushtit paraqet një kusht dhe përmban funksione ose konstante agregate.
Përdorimi i klauzolës HAVING në lidhje me funksionin agregat COUNT (*) tregohet në shembullin e mëposhtëm:
PËRDORIMI SampleDb; - Kthen "p3" ZGJIDH numrin e projektit NGA Works_on GROUP BY ProjectNumber HAVING COUNT (*)
Në këtë shembull, sistemi përdor klauzolën GROUP BY për të grupuar të gjitha rreshtat sipas vlerave në kolonën Numri i projektit. Më pas numërohet numri i rreshtave në secilin grup dhe zgjidhen grupet që përmbajnë më pak se katër rreshta (tre ose më pak).
Klauzola HAVING mund të përdoret gjithashtu pa funksione agregate, siç tregohet në shembullin më poshtë:
PËRDORIMI SampleDb; - Do të kthejë "Konsulent" ZGJIDH Punë NGA Works_on GRUPI BY Job HAVING Job LIKE "К%";
Ky shembull grupon rreshtat e tabelës Works_on sipas titullit të punës dhe eliminon punët që nuk fillojnë me shkronjën "K".
Klauzola HAVING mund të përdoret gjithashtu pa klauzolën GROUP BY, megjithëse kjo nuk është praktikë e zakonshme. Në këtë rast, të gjitha rreshtat e tabelës kthehen në të njëjtin grup.
RENDI SIPAS klauzolës
Fjali URDHËR NGA përcakton rendin e renditjes së rreshtave në grupin e rezultateve të kthyera nga pyetja. Kjo klauzolë ka sintaksën e mëposhtme:
Rendi i renditjes është specifikuar në parametrin col_name. Col_number është një tregues alternativ i renditjes që identifikon kolonat në rendin në të cilin shfaqen në listën e përzgjedhjes së deklaratës SELECT (1 është kolona e parë, 2 është kolona e dytë, e kështu me radhë). Parametri ASC përcakton një rend rritës, dhe Parametri DESC- në rrjedhën e poshtme. Parazgjedhja është ASC.
Emrat e kolonave në klauzolën ORDER BY nuk duhet të jenë në listën e kolonave të zgjedhura. Por kjo nuk vlen për pyetjet si SELECT DISTINCT, pasi në pyetje të tilla, emrat e kolonave të specifikuara në klauzolën ORDER BY duhet të shfaqen gjithashtu në listën e kolonave të zgjedhura. Përveç kësaj, kjo klauzolë nuk mund të përmbajë emra kolonash nga tabelat që nuk janë të specifikuara në klauzolën FROM.
Siç mund ta shihni nga sintaksa e klauzolës ORDER BY, renditja e grupit të rezultateve mund të bëhet në kolona të shumta. Ky renditje tregohet në shembullin e mëposhtëm:
Ky shembull zgjedh numrat e departamenteve dhe mbiemrat dhe emrat e punonjësve për punonjësit, numri i personelit të të cilëve është më pak se 20,000, si dhe renditet sipas mbiemrit dhe emrit. Rezultati i kësaj pyetjeje:
Kolonat në klauzolën ORDER BY mund të specifikohen jo me emrat e tyre, por sipas renditjes në listën e përzgjedhjes. Prandaj, fjalia në shembullin e mësipërm mund të rishkruhet si më poshtë:
Kjo mënyrë alternative e specifikimit të kolonave sipas pozicionit të tyre në vend të emrave përdoret nëse kriteri i renditjes përmban një funksion agregat. (Një mënyrë tjetër është përdorimi i emrave të kolonave, të cilët shfaqen më pas në klauzolën RENDI SIPAS.) Megjithatë, në klauzolën ORDER BY, rekomandohet që të specifikoni kolonat me emrat e tyre dhe jo me numra, për ta bërë më të lehtë përditësimin e pyetjes. nëse keni nevojë të shtoni ose hiqni kolona në listën e përzgjedhur. Specifikimi i kolonave në klauzolën ORDER BY me numrat e tyre tregohet në shembullin më poshtë:
PËRDORIMI SampleDb; ZGJIDH numrin e projektit, COUNT (*) "Numri i punonjësve" NGA Works_on GRUPI SIPAS Numri i projektit RENDOSJE NGA 2 DESC;
Këtu, për çdo projekt, zgjidhet numri i projektit dhe numri i punonjësve pjesëmarrës në të, duke renditur rezultatin në rend zbritës me numrin e punonjësve.
Transact-SQL vendos vlerat NULL në krye të listës kur renditen në rend rritës dhe vlerat NULL në fund të listës kur renditen në rend zbritës.
Përdorimi ORDER BY Klauzole për të faqezuar rezultatet
Shfaqja e rezultateve të pyetjeve në faqen aktuale mund të zbatohet ose në një aplikacion të personalizuar, ose mund ta udhëzoni serverin e bazës së të dhënave ta bëjë këtë. Në rastin e parë, të gjitha rreshtat e bazës së të dhënave dërgohen në aplikacion, detyra e të cilit është të zgjedhë rreshtat e kërkuar dhe t'i shfaqë ato. Në rastin e dytë, vetëm rreshtat e kërkuar për faqen aktuale merren dhe shfaqen nga ana e serverit. Siç mund ta prisni, krijimi i faqeve në anën e serverit zakonisht ofron performancën më të mirë sepse klientit i dërgohen vetëm linjat e nevojshme për shfaqje.
Për të mbështetur krijimin e faqeve nga ana e serverit, SQL Server 2012 prezanton dy klauzola të reja SELECT: OFFSET dhe FETCH. Zbatimi i këtyre dy fjalive tregohet në shembullin e mëposhtëm. Këtu, nga baza e të dhënave AdventureWorks2012 (të cilën mund ta gjeni në burim), nxjerr ID-në e biznesit, titullin e punës dhe ditëlindjen e të gjitha punonjëseve femra, të renditura sipas titullit të punës në rend rritës. Grupi i rreshtave që rezulton ndahet në faqe me 10 rreshta dhe shfaqet një faqe e tretë:
Në një fjali OFFSET specifikon numrin e rreshtave të rezultateve që duhet të kapërcehen në rezultatin e shfaqur. Kjo shumë llogaritet pasi rreshtat janë renditur me klauzolën RENDI SIPAS. Në një fjali MERR TJETËR specifikon numrin e WHERE dhe rreshtave të renditura për t'u kthyer. Parametri i kësaj klauzole mund të jetë një konstante, një shprehje ose rezultat i një pyetjeje tjetër. Klauzola FETCH NEXT është e ngjashme me klauzolën MERRNI SË PARË.
Qëllimi kryesor kur krijohen faqe në anën e serverit është që të jemi në gjendje të implementojmë forma të zakonshme të faqeve duke përdorur variabla. Kjo detyrë mund të realizohet përmes paketës SQL Server.
SELECT Deklarata dhe prona IDENTITET
prona IDENTITET ju lejon të përcaktoni vlerat për një kolonë specifike të tabelës në formën e një numëruesi automatik në rritje. Kolonat e një lloji të të dhënave numerike si TINYINT, SMALLINT, INT dhe BIGINT mund ta kenë këtë veti. Për një kolonë të tillë tabele, motori i bazës së të dhënave gjeneron automatikisht vlera sekuenciale duke filluar me vlerën fillestare të specifikuar. Prandaj, vetia IDENTITY mund të përdoret për të gjeneruar vlera numerike njëshifrore për kolonën e zgjedhur.
Një tabelë mund të përmbajë vetëm një kolonë me vetinë IDENTITY. Pronari i tabelës ka aftësinë të specifikojë vlerën fillestare dhe rritjen, siç tregohet në shembullin më poshtë:
PËRDORIMI SampleDb; KRIJO TABELA Produkti (ID INT IDENTITET (10000, 1) JO NULL, Emri NVARCHAR (30) JO NULL, PARA ÇMIMI) INSERT NË PRODHIM (Emri, Çmimi) VLERAT ("Produkti1", 10), ("Produkti2", 15) , ("Produkti3", 8), ("Produkti4", 15), ("Produkti5", 40); - Do të kthejë 10004 SELECT IDENTITYCOL FROM Product WHERE Emri = "Product5"; - Analoge me deklaratën e mëparshme ZGJIDHni identitetin e $ FROM Produkti WHERE Emri = "Produkt5";
Ky shembull fillimisht krijon një tabelë Produkti që përmban një kolonë ID me një veçori IDENTITY. Vlerat në kolonën Id krijohen automatikisht nga sistemi, duke filluar nga 10,000 dhe duke u rritur me një hap për çdo vlerë pasuese: 10,000, 10,001, 10,002, etj.
Disa funksione dhe variabla të sistemit shoqërohen me veçorinë IDENTITY. Për shembull, përdoret shembulli i kodit ndryshorja e sistemit $ identitet... Siç mund ta shihni nga rezultatet e ekzekutimit të këtij kodi, kjo variabël i referohet automatikisht veçorisë IDENTITY. Në vend të kësaj, mund të përdorni gjithashtu funksionin e sistemit IDENTITETI.
Vlera fillestare dhe rritja e kolonës me vetinë IDENTITY mund të gjenden duke përdorur funksionet IDENT_SEED dhe IDENT_INCR përkatësisht. Këto funksione zbatohen si më poshtë:
PËRDORIMI SampleDb; SELECT IDENT_SEED ("Produkt"), IDENT_INCR ("Produkt")
Siç u përmend, vlerat e IDENTITY vendosen automatikisht nga sistemi. Por përdoruesi mund të specifikojë në mënyrë eksplicite vlerat e tyre për vargje të caktuara duke i caktuar parametrit IDENTITY_INSERT vlera ON para futjes së vlerës eksplicite:
SET IDENTITY INSERT emrin e tabelës ON
Për shkak se parametri IDENTITY_INSERT mund të përdoret për të vendosur çdo vlerë, duke përfshirë vlerat e dyfishta, në një kolonë me veçorinë IDENTITY, vetia IDENTITY zakonisht nuk zbaton veçantinë e vlerave të kolonës. Prandaj, kufizimet UNIQUE ose PRIMARY KEY duhet të zbatohen për të zbatuar veçantinë e vlerave të kolonës.
Kur futni vlera në një tabelë pasi keni vendosur IDENTITY_INSERT në aktiv, sistemi krijon vlerën tjetër në kolonën IDENTITY, duke rritur vlerën më të lartë aktuale në atë kolonë.
Deklarata CREATE SEQUENCE
Përdorimi i vetive IDENTITY ka disa të meta të rëndësishme, më të rëndësishmet prej të cilave janë:
prona është e kufizuar në tabelën e specifikuar;
vlera e re e kolonës nuk mund të merret në asnjë mënyrë tjetër, përveçse duke e aplikuar atë;
vetia IDENTITY mund të specifikohet vetëm kur krijohet një kolonë.
Për këto arsye, SQL Server 2012 prezanton sekuenca që kanë të njëjtën semantikë si vetia IDENTITY, por pa disavantazhet e listuara më parë. Në këtë kontekst, një sekuencë i referohet funksionalitetit të një baze të dhënash që ju lejon të specifikoni vlerat e kundërta për objekte të ndryshme të bazës së të dhënave, të tilla si kolonat dhe variablat.
Sekuencat krijohen duke përdorur udhëzimin KRIJO SEKUENCE... Deklarata CREATE SEQUENCE përcaktohet në standardin SQL dhe mbështetet nga sisteme të tjera të bazës së të dhënave relacionale si IBM DB2 dhe Oracle.
Shembulli më poshtë tregon se si të krijoni një sekuencë në SQL Server:
PËRDORIMI SampleDb; KRIJO SEKUENCE dbo.Sekuenca1 SI NË FILLIM ME 1 RRITJE ME 5 MINVLERË 1 MAXVLERË 256 CIKLE;
Në shembullin e mësipërm, vlerat e Sekuencës 1 gjenerohen automatikisht nga sistemi, duke filluar me një vlerë prej 1 dhe në rritje prej 5 për çdo vlerë të njëpasnjëshme. Kështu, në klauzolën FILLIM tregohet vlera fillestare dhe në Oferta RRITJE- hap. (Hapi mund të jetë pozitiv ose negativ.)
Në dy fjalitë e ardhshme fakultative MINVALUES dhe MAXVLERA specifikon vlerën minimale dhe maksimale të objektit të sekuencës. (Vini re se MINVALUE duhet të jetë më e vogël ose e barabartë me vlerën fillestare dhe MAXVALUE nuk mund të jetë më e madhe se kufiri i sipërm i llojit të të dhënave të specifikuar për sekuencën.) Në një fjali CIKLI tregon se sekuenca përsëritet nga fillimi kur tejkalohet vlera maksimale (ose minimale për një sekuencë me hap negativ). Si parazgjedhje, kjo klauzolë është vendosur në NO CYCLE, që do të thotë se tejkalimi i vlerës maksimale ose minimale të sekuencës krijon një përjashtim.
Tipari kryesor i sekuencave është pavarësia e tyre nga tabelat, d.m.th. ato mund të përdoren me çdo objekt të bazës së të dhënave siç janë kolonat e tabelës ose variablat. (Kjo veti ka një efekt pozitiv në ruajtjen dhe, për rrjedhojë, në performancën. Një sekuencë specifike nuk ka nevojë të ruhet; ruhet vetëm vlera e fundit e saj.)
Vlerat e reja të sekuencës krijohen me VLERA E TJETËR PËR shprehjet, aplikimi i të cilit tregohet në shembullin e mëposhtëm:
PËRDORIMI SampleDb; - Kthen 1 ZGJIDH VLERËN TJETËR PËR dbo.sequence1; - Kthen 6 (hapi tjetër) ZGJIDH VLERËN E TJETËR PËR dbo.sequence1;
Ju mund të përdorni shprehjen NEXT VALUE FOR për të caktuar rezultatin e një sekuence në një variabël ose qelizë kolone. Shembulli i mëposhtëm ilustron përdorimin e kësaj shprehjeje për të caktuar rezultatet në një kolonë:
PËRDORIMI SampleDb; KRIJO TABELA Produkti (Id INT NOT NULL, Emri NVARCHAR (30) JO NULL, PARA Çmimi) FUT NE VLERAT E PRODUKTIT (VLERA TJETËR PËR dbo.sequence1, "Product1", 10); INSERT NË VLERAT E PRODUKTIT (VLERA E TJETËR PËR dbo.sequence1, "Product2", 15); -...
Shembulli i mësipërm fillimisht krijon një tabelë të quajtur Produkt me katër kolona. Më pas, dy deklarata INSERT futin dy rreshta në këtë tabelë. Dy qelizat e para të kolonës së parë do të jenë 11 dhe 16.
Shembulli i mëposhtëm tregon përdorimin e pamjes së katalogut sys.sekuenca për të parë vlerën aktuale të një sekuence pa e përdorur atë:
Në mënyrë tipike, shprehja NEXT VALUE FOR përdoret në një deklaratë INSERT për të udhëzuar sistemin të futë vlerat e krijuara. Kjo shprehje mund të përdoret gjithashtu si pjesë e një pyetjeje me shumë rreshta duke përdorur klauzolën OVER.
Për të ndryshuar një veti të një sekuence ekzistuese, aplikoni Deklarata ALTER SEQUENCE... Një nga përdorimet më të rëndësishme për këtë deklaratë është me opsionin RISTART WITH, i cili rivendos sekuencën e specifikuar. Shembulli i mëposhtëm tregon përdorimin e deklaratës ALTER SEQUENCE për të rivendosur pothuajse të gjitha vetitë e Sekuencës1:
PËRDORIMI SampleDb; SEKUENCA NDRYSHIME dbo.sekuenca1 RIFIKONI ME 100 RRITJE ME 50 MINVLERË 50 MAXVLERË 200 PA CIKLI;
Sekuenca fshihet duke përdorur udhëzimin SEKUENCA E RËZIMIT.
Vendosni operatorët
Përveç operatorëve të diskutuar më parë, Transact-SQL mbështet tre operatorë të tjerë të grupeve: UNION, INTERSECT dhe EXCEPT.
Operatori UNION
Operatori UNION kombinon rezultatet e dy ose më shumë pyetjeve në një grup të vetëm rezultatesh që përfshin të gjitha rreshtat që u përkasin të gjitha pyetjeve në bashkim. Rrjedhimisht, rezultati i bashkimit të dy tabelave është një tabelë e re që përmban të gjitha rreshtat në njërën ose të dyja tabelat origjinale.
Forma e përgjithshme e operatorit UNION duket si kjo:
select_1 UNION zgjidhni_2 (select_3]) ...
Opsionet select_1, select_2, ... janë deklarata SELECT që krijojnë bashkimin. Nëse përdoret parametri ALL, shfaqen të gjitha rreshtat, duke përfshirë dublikatat. Në deklaratën UNION, parametri ALL ka të njëjtin kuptim si në SELECT, por me një ndryshim: ky është parazgjedhja për një SELECT, por duhet të specifikohet në mënyrë eksplicite për UNION.
Në formën e tij origjinale, SampleDb nuk është i përshtatshëm për të demonstruar përdorimin e operatorit UNION. Prandaj, ky seksion krijon një tabelë të re EmployeeEnh që është identike me tabelën ekzistuese Employee, por ka një kolonë shtesë City. Kjo kolonë tregon vendbanimin e punonjësve.
Krijimi i tabelës EmployeeEnh na ofron një mundësi për të demonstruar përdorimin e klauzolës. NË në deklaratën SELECT. Deklarata SELECT INTO kryen dy operacione. Së pari, krijohet një tabelë e re me kolonat e listuara në listën SELECT. Pastaj rreshtat nga tabela origjinale futen në tabelën e re. Emri i tabelës së re është specifikuar në klauzolën INTO, dhe emri i tabelës burimore është specifikuar në klauzolën FROM.
Shembulli më poshtë tregon krijimin e tabelës EmployeeEnh nga tabela Employee:
PËRDORIMI SampleDb; SELECT * INTO EmployeeEnh FROM Employee; ALTER TABLE PunonjësEnh SHTO Qyteti NCHAR (40) NULL;
Në këtë shembull, deklarata SELECT INTO krijon tabelën EmployeeEnh, fut të gjitha rreshtat nga tabela e burimit Employee në të dhe më pas deklarata ALTER TABLE shton kolonën City në tabelën e re. Por kolona e shtuar Qyteti nuk përmban asnjë vlerë. Vlerat mund të futen në këtë kolonë përmes Management Studio ose duke përdorur kodin e mëposhtëm:
PËRDORIMI SampleDb; PËRDITËZIM EmployeeEnh SET City = "Kazan" WHERE Id = 2581; PËRDITËSOJE EmployeeEnh SET City = "Moscow" WHERE Id = 9031; PËRDITËSOJE EmployeeEnh SET Qyteti = "Yekaterinburg" WHERE Id = 10102; UPDATE EmployeeEnh SET City = "Shën Petersburg" WHERE Id = 18316; UPDATE EmployeeEnh SET City = "Krasnodar" WHERE Id = 25348; PËRDITËSIM EmployeeEnh SET Qyteti = "Kazan" WHERE Id = 28559; UPDATE EmployeeEnh SET City = "Perm" WHERE Id = 29346;
Tani jemi gati të demonstrojmë përdorimin e deklaratës UNION. Shembulli më poshtë tregon një pyetje për të krijuar një bashkim midis tabelave EmployeeEnh dhe Department duke përdorur këtë deklaratë:
PËRDORIMI SampleDb; ZGJIDH Qyteti AS "Qytet" NGA PunonjësitEnh UNION ZGJEDH vendndodhjen NGA Departamenti;
Rezultati i kësaj pyetjeje:
Vetëm tabelat e përputhshme mund të bashkohen duke përdorur deklaratën UNION. Me tabela të pajtueshme, nënkuptojmë që të dyja listat e kolonave në përzgjedhje duhet të përmbajnë të njëjtin numër kolonash dhe kolonat përkatëse duhet të kenë lloje të dhënash të përputhshme. (Për sa i përket përputhshmërisë, llojet e të dhënave INT dhe SMALLINT nuk janë të përputhshme.)
Rezultati i lidhjes mund të renditet vetëm duke përdorur klauzolën ORDER BY në deklaratën e fundit SELECT, siç tregohet në shembullin më poshtë. Klauzolat GROUP BY dhe HAVING mund të përdoren me deklarata individuale SELECT, por jo brenda vetë bashkimit.
Pyetja në këtë shembull zgjedh punonjësit që ose punojnë në departamentin d1 ose që kanë filluar të punojnë në një projekt përpara 1 janarit 2008.
Operatori UNION mbështet opsionin ALL. Ky opsion nuk heq dublikatat nga grupi i rezultateve. Një operator OR mund të përdoret në vend të operatorit UNION nëse të gjitha deklaratat SELECT të bashkuara nga një ose më shumë operatorë UNION i referohen të njëjtës tabelë. Në këtë rast, grupi i deklaratave SELECT zëvendësohet nga një deklaratë e vetme SELECT me një grup deklaratash OR.
Operatorët INTERSECT dhe EXCEPT
Dy operatorë të tjerë për të punuar me grupe, KRYQËZOHET dhe PËRVEÇ, përcaktoni përkatësisht kryqëzimin dhe ndryshimin. Nën kryqëzimin, në këtë kontekst, ka një grup rreshtash që u përkasin të dy tabelave. Dhe ndryshimi midis dy tabelave përcaktohet si të gjitha vlerat që i përkasin tabelës së parë dhe nuk janë të pranishme në të dytën. Shembulli i mëposhtëm tregon përdorimin e operatorit INTERSECT:
Transact-SQL nuk e mbështet përdorimin e parametrit ALL as me operatorët INTERSECT ose EXCEPT. Përdorimi i operatorit EXCEPT tregohet në shembullin e mëposhtëm:
Mos harroni se këta tre operatorë grupesh kanë përparësi të ndryshme ekzekutimi: INTERSECT ka përparësinë më të lartë, e ndjekur nga EXCEPT dhe UNION ka përparësinë më të ulët. Dështimi për t'i kushtuar vëmendje përparësisë së ekzekutimit kur përdorni disa operatorë të ndryshëm grupesh mund të çojë në rezultate të papritura.
Shprehjet RAST
Në fushën e programimit të aplikacionit të bazës së të dhënave, ndonjëherë është e nevojshme të modifikohet prezantimi i të dhënave. Për shembull, njerëzit mund të ndahen sipas klasës së tyre shoqërore, duke përdorur vlerat 1, 2 dhe 3, që tregojnë përkatësisht burra, gra dhe fëmijë. Kjo teknikë programimi mund të zvogëlojë kohën e nevojshme për zbatimin e programit. Shprehje RASTE Transact-SQL ju lejon të zbatoni me lehtësi këtë lloj kodimi.
Ndryshe nga shumica e gjuhëve programuese, CASE nuk është një deklaratë, por një shprehje. Prandaj, një shprehje CASE mund të përdoret pothuajse kudo ku Transact-SQL lejon përdorimin e shprehjeve. Shprehja CASE ka dy forma:
shprehje e thjeshtë RAST;
shprehja e kërkimit RAST.
Sintaksa për një shprehje të thjeshtë CASE është si më poshtë:
Një deklaratë me një shprehje të thjeshtë CASE fillimisht kërkon listën e të gjitha shprehjeve në klauzola KUR shprehja e parë që përputhet me shprehjen_1 dhe më pas ekzekuton atë përkatëse PASTAJ klauzolë... Nëse nuk ka asnjë shprehje që përputhet në listën WHEN, atëherë klauzolë TJETËR.
Sintaksa për një shprehje kërkimi CASE është si më poshtë:
Në këtë rast, ai kërkon kushtin e parë kualifikues dhe më pas ekzekuton klauzolën përkatëse THEN. Nëse asnjë nga kushtet nuk i plotëson kërkesat, klauzola ELSE ekzekutohet. Përdorimi i shprehjes së kërkimit CASE tregohet në shembullin e mëposhtëm:
PËRDORIMI SampleDb; ZGJIDH emrin e projektit, RASTIN KUR Buxheti> 0 DHE Buxheti 100000 DHE Buxheti 150000 DHE Buxheti
Rezultati i kësaj pyetjeje:
Ky shembull peshon buxhetet e të gjitha projekteve dhe shfaq peshat e llogaritura së bashku me emrat përkatës të projekteve.
Shembulli më poshtë tregon një mënyrë tjetër për të përdorur një shprehje CASE ku klauzola WHEN përmban nënpyetje që janë pjesë e shprehjes:
PËRDORIMI SampleDb; SELECT Emri i Projektit, RASTI KUR p1.Buxheti (SELECT AVG (p2.Budget) FROM Project p2) PAS "mbi mesataren" FUND "Kategoria e buxhetit" FROM Projekti p1;
Rezultati i kësaj pyetjeje është si më poshtë:
Për të përmbledhur informacionin e përfshirë në bazën e të dhënave, SQL ofron funksione agregate. Një funksion agregat merr një kolonë të tërë të dhënash si argument dhe kthen një vlerë të vetme që e përmbledh atë kolonë në një mënyrë specifike.
Për shembull, funksioni agregat AVG () merr një kolonë numrash si argument dhe llogarit mesataren e tyre.
Për të llogaritur të ardhurat mesatare për frymë të një banori të Zelenogradit, ju nevojitet pyetja e mëposhtme:
ZGJIDH "TË ARDHURAT MESATORE =", AVG (SUMD)
SQL ka gjashtë funksione agregate që ofrojnë lloje të ndryshme përmbledhjesh (Figura 1):
- SUM () llogarit shumën e të gjitha vlerave të përfshira në kolonë;
- AVG () llogarit mesataren midis vlerave të përmbajtura në kolonë;
- MIN () gjen më të voglin midis të gjitha vlerave të përfshira në kolonë;
- MAX () gjen më të madhin midis të gjitha vlerave të përfshira në kolonë;
- COUNT () numëron numrin e vlerave që përmbahen në kolonë;
- COUNT (*) numëron numrin e rreshtave në tabelën e rezultateve të pyetjes.
Argumenti për një funksion agregat mund të jetë një emër i thjeshtë kolone, si në shembullin e mëparshëm, ose një shprehje, si në pyetjen e mëposhtme që llogarit taksën për frymë:
SELECT AVG (SUMD * 0.13)
Ky pyetje krijon një kolonë të përkohshme që përmban vlerat (SUMD * 0.13) për çdo rresht në tabelën PERSON, dhe më pas llogarit mesataren e kolonës së përkohshme.
Shuma e të ardhurave të të gjithë banorëve të Zelenograd mund të llogaritet duke përdorur funksionin agregat SUM:
ZGJIDH SHUMËN (SHUMË) NGA PERSONI
Funksioni agregat mund të përdoret gjithashtu për të llogaritur totalet për një tabelë të rezultateve të marra duke bashkuar disa tabela burimore. Për shembull, ju mund të llogaritni shumën totale të të ardhurave që banorët marrin nga një burim i quajtur "Bursa":
ZGJIDH SHUMEN (PARAJA)
NGA FITIMI, HAVE_D
KU PROFIT.ID = HAVE_D.ID
AND PROFIT.SOURCE = 'Bursë'
Funksionet agregate MIN () dhe MAX () ju lejojnë të gjeni, respektivisht, vlerat më të vogla dhe më të mëdha në tabelë. Sidoqoftë, kolona mund të përmbajë vlera numerike ose vargje, ose vlera datë ose orë.
Për shembull, mund të përcaktoni:
(a) të ardhurat totale më të ulëta të fituara nga rezidentët dhe tatimi më i lartë i pagueshëm:
SELECT MIN (SUMD), MAX (SUMD * 0,13)
(b) datat e lindjes së banorit më të vjetër dhe më të ri:
ZGJIDH MINI (RDATE), MAX (RDATE)
(c) mbiemrat, emrat dhe patronimet e banorëve të parë dhe të fundit në listë, të renditura sipas alfabetit:
ZGJIDH MIN (FIO), MAX (FIO)
Kur aplikoni këto funksione agregate, duhet të mbani mend se të dhënat numerike krahasohen sipas rregullave aritmetike, datat krahasohen në mënyrë sekuenciale (vlerat e mëparshme të datës konsiderohen më të vogla se ato të mëvonshme), intervalet kohore krahasohen në bazë të kohëzgjatjes së tyre.
Kur përdorni MIN () dhe MAX () me të dhëna vargu, rezultati i krahasimit të dy vargjeve varet nga tabela e kodimit të karaktereve të përdorur.
Funksioni agregat COUNT () numëron numrin e vlerave në një kolonë të çdo lloji:
(a) sa apartamente ka në mikrodistriktin 1?
ZGJIDH NUMRIN (ADR) FROM FLAT WHERE ADR LIKE "%, 1_ _-%"
(b) sa banorë kanë burime të ardhurash?
ZGJIDH NUMRIN (NOM I NDRYSHME) NGA HAVE_D
(c) sa burime të ardhurash përdoren nga banorët?
SELECT COUNT (ID DISTINCT) FROM HAVE_D (Fjala kyçe DISTINCT specifikon që vlerat jo dublikatë në kolonë numërohen).
Funksioni i agregatit special COUNT (*) numëron rreshtat në tabelën e rezultateve, jo vlerat e të dhënave:
(a) sa apartamente ka në mikrodistriktin e dytë?
ZGJIDH NUMRIN (*) FROM FLAT WHERE ADR LIKE "%, 2 __-%"
(b) sa burime të ardhurash ka Ivan Ivanovich?
ZGJIDH NUMRIN (*) NGA PERSONI, HAVE_D WHERE FIO = "Ivanov Ivan Ivanovich" DHE PERSON.NOM = HAVE_D.NOM
(c) sa banorë jetojnë në një apartament në një adresë të caktuar?
ZGJIDH NUMRIN (*) FROM PERSON WHERE ADR = "Zelenograd, 1001-45"
Një mënyrë për të kuptuar se si ekzekutohen pyetjet agregate është të mendojmë për ekzekutimin e pyetjeve në dy pjesë. Së pari, përcaktohet se si pyetja do të funksiononte pa funksione agregate, duke kthyer rreshta të shumtë të rezultateve. Funksionet e grumbulluara më pas aplikohen në rezultatet e pyetjes, duke kthyer një rresht të vetëm përmbledhës.
Për shembull, merrni parasysh pyetjen komplekse të mëposhtme: gjeni të ardhurat totale mesatare për frymë, shumën e të ardhurave totale të banorëve dhe të ardhurat mesatare të burimit si përqindje e të ardhurave totale të banorit. Përgjigja jepet nga operatori
SELECT AVG (SUMD), SUM (SUMD), (100 * AVG (PARA / SUMD)) FROM PERSON, FITIMI, HAVE_D WHERE PERSON.NOM = HAVE_D.NOM DHE HAVE_D.ID = PROFIT.ID
Pa funksione agregate, pyetja do të dukej kështu:
ZGJIDH SUMD, SUMD, PARA / SUMD FROM PERSON, FITIMI, HAVE_D WHERE PERSON.NOM = HAVE_D.NOM DHE HAVE_D.ID = PROFIT.ID
dhe do të kthente një rresht rezultatesh për çdo banor dhe burim specifik të ardhurash. Funksionet agregate përdorin kolonat në tabelën e rezultateve të këtij pyetësori për të prodhuar një tabelë me një rresht me rezultatet përmbledhëse.
Në rreshtin e kolonave të kthyera, mund të specifikoni një funksion agregat në vend të çdo emri kolone. Për shembull, mund të përfshihet në një shprehje që shton ose zbret vlerat e dy funksioneve agregate:
SELECT MAX (SUMD) -MIN (SUMD) NGA PERSONI
Megjithatë, një funksion agregat nuk mund të jetë një argument për një funksion tjetër agregat, d.m.th. Funksionet agregate të mbivendosur janë të ndaluara.
Për më tepër, funksionet agregate dhe emrat e kolonave të rregullta nuk mund të përdoren në listën e kolonave të kthyera në të njëjtën kohë, pasi nuk ka asnjë pikë në këtë, për shembull:
ZGJIDH FIO, SHUMË (SHUMË) NGA PERSONI
Këtu, elementi i parë i listës udhëzon DBMS të krijojë një tabelë që do të përbëhet nga disa rreshta dhe do të përmbajë një rresht për çdo banor. Artikulli i dytë në listë kërkon që DBMS të marrë një vlerë rezultati, që është shuma e vlerave në kolonën SUMD. Këto dy udhëzime kundërshtojnë njëra-tjetrën, duke rezultuar në një gabim.
Sa më sipër nuk zbatohet për rastet e përpunimit të nënpyetjeve dhe pyetjeve me grupim.
