Funksioni SUM në gjuhën SQL, megjithë thjeshtësinë e tij, përdoret mjaft shpesh kur punoni me një bazë të dhënash. Me ndihmën e tij, është i përshtatshëm për të marrë disa rezultate të ndërmjetme ose përfundimtare pa përdorur ndihmën e mjeteve ndihmëse DBMS.

Sintaksa e funksionit

Në shumicën e gjuhëve SQL, sintaksa e shumës është e njëjtë - vetëm emri i fushës ose ndonjë veprim aritmetik i disa prej tyre përdoret si argument, mbi të cilin kërkohet përmbledhja.

Në raste të jashtëzakonshme, ju mund të transferoni një vlerë specifike në formën e një numri ose një ndryshoreje, megjithatë, "skema" të tilla praktikisht nuk përdoren, pasi ato nuk kanë vlerë të madhe. Më poshtë është sintaksa e funksionit në Gjuha SQL:

shuma (a) - këtu përdoret një vlerë ose shprehje numerike si parametër

Vlen të përmendet se para parametrit, mund të vendosni fjalë kyçe, për shembull, DISTINCT ose ALL, të cilat do të marrin respektivisht vetëm unike ose të gjitha vlerat.

Një shembull i përdorimit të SUM në SQL

Për një kuptim përfundimtar se si funksionon funksioni, ia vlen të merren parasysh disa shembuj. Në SQL, SUM mund të përdoret si rezultat i kthimit ashtu edhe si vlerë e ndërmjetme, për shembull, për të kontrolluar një kusht.

Për rastin e parë, merrni parasysh opsionin kur dëshironi të ktheni shumën e shitjeve për çdo produkt, duke qenë se numri i blerjeve të bëra mund të jetë në shumës. Për të marrë rezultatin, do të mjaftojë të ekzekutoni pyetjen e mëposhtme:

ZGJIDH produktin, shuma (Shuma e blerjes) NGA Grupet e Shitjeve sipas Produktit;

Përgjigja për këtë komandë do të ketë një listë unike të produkteve me shumën totale të blerjeve për secilën prej tyre.

Për shembullin e dytë, ju duhet të merrni një listë të produkteve, sasia e shitjeve për të cilat ka tejkaluar një vlerë të caktuar, për shembull, 100. Ju mund të merrni rezultatin për këtë detyrë në disa mënyra, më optimale prej të cilave është ekzekutimi i një kërkese:

ZGJIDHni artikullin FROM (ZGJIDHni artikullin, shuma (Shuma e blerjes) si shuma nga shitjet) WHERE Shuma> 100.

LLOGARITJE

Funksionet përmbledhëse

Në shprehjet e pyetjeve SQL, shpesh ju duhet të ekzekutoni parapërpunimit të dhëna. Për këtë qëllim përdoren funksione dhe shprehje të veçanta.

Shumë shpesh, ju duhet të zbuloni se sa regjistrime përputhen me një pyetje të veçantë,sa është shuma e vlerave të një kolone të caktuar numerike, vlerat maksimale, minimale dhe mesatare të saj. Për këtë përdoren të ashtuquajturat funksione përmbledhëse (statistikore, agregate). Funksionet përmbledhëse përpunojnë grupe të dhënash të specifikuara nga një klauzolë WHERE, për shembull. Nëse ato përfshihen në listën e kolonave pas deklaratës SELECT, tabela që rezulton do të përmbajë jo vetëm kolonat e tabelës së bazës së të dhënave, por edhe vlerat e llogaritura duke përdorur këto funksione. Më poshtë ështëlista e funksioneve përmbledhëse.

• COUNT (parametër ) - kthen numrin e regjistrimeve të specifikuara në parametër. Nëse dëshironi të merrni numrin e të gjitha rekordeve, duhet të specifikoni një yll (*) si parametër. Nëse specifikoni një emër kolone si parametër, funksioni do të kthejë numrin e regjistrimeve në të cilat kjo kolonë ka vlera të tjera nga NULL. Për të zbuluar se sa vlera të dallueshme përmban një kolonë, paraprini emrin e saj me fjalën kyçe DISTINCT. Për shembull:

ZGJIDH NUMRIN (*) NGA Klientët;

SELECT COUNT (porosia_samount) NGA Klientët;

ZGJIDH NUMRIN (Shumë_porosie TË NDRYSHME) NGA Klientët;

Përpjekja për të ekzekutuar pyetjen e mëposhtme do të rezultojë në një mesazh gabimi:

SELECT Rajoni, COUNT (*) NGA Klientët;

• SUM (parametri ) - kthen shumën e vlerave të kolonës së specifikuar në parametër. Parametri mund të jetë gjithashtu një shprehje që përmban emrin e kolonës. Për shembull:

ZGJIDH SHUMEN (Suma_porosi) NGA Klientët;

Kjo shprehje SQL kthen një tabelë me një kolonë, një rekord që përmban shumën e të gjitha vlerave të përcaktuara në kolonën Order_Sum nga tabela e klientëve.

Supozoni se në tabelën e burimit, vlerat në kolonën Order_Amount shprehen në rubla, dhe ne duam të llogarisim shumën totale në dollarë. Nëse kursi aktual i këmbimit është, për shembull, 27.8, atëherë mund të merrni rezultatin e kërkuar duke përdorur shprehjen:

ZGJIDH SHUMEN (Suma_porosit * 27.8) NGA Klientët;

• AVG (parametri ) - kthen mesataren aritmetike të të gjitha vlerave të kolonës së specifikuar në parametër. Parametri mund të jetë një shprehje që përmban emrin e kolonës. Për shembull:

SELECT AVG (Suma_porosi) FROM Clients;

SELECT AVG (Suma_porosi * 27.8) NGA Klientët

KU Rajoni<>"Veriu_3perëndim";

• MAX (parametër ) - kthen vlerën maksimale në kolonën e specifikuar në parametër. Parametri mund të jetë gjithashtu një shprehje që përmban emrin e kolonës. Për shembull:

ZGJIDH MAX (Shumë_porosi) NGA Klientët;

SELECT MAX (Suma_porosi * 27,8) NGA Klientët

KU KETU Rajon<>"Veriu_3perëndim";

• MIN (parametër ) - kthen vlerën minimale në kolonën e specifikuar në parametër. Parametri mund të jetë një shprehje që përmban emrin e kolonës. Për shembull:

SELECT MIN (Suma_porosi) FROM Klientët;

SELECT MIN (Sasia_porosia * 27,8) NGA Klientët

KU KETU Rajon<>"Veriu_3perëndim";

Në praktikë, shpesh kërkohet të merret një tabelë përmbledhëse që përmban vlerat totale, mesatare, maksimale dhe minimale të kolonave numerike. Për ta bërë këtë, duhet të përdorni funksionet e grupimit (GROUP BY) dhe përmbledhjes.

ZGJIDH Rajonin, SUM (Suma_porosi) NGA Klientët

GRUPI SIPAS Rajoni;

Tabela e rezultateve për këtë pyetje përmban emrat e rajoneve dhe sasinë totale (gjithsej) të porosive të të gjithë klientëve nga rajonet përkatëse (Fig. 5).

Tani le të shohim një kërkesë për të marrë të gjitha totalet sipas rajonit:

SELECT Rajoni, SUM (Suma_porosi), AVG (Sasia_porosi), MAX (Shuma_porosi), MIN (Suma_porosi)

NGA Klientët

GRUPI SIPAS Rajoni;

Tabelat fillestare dhe përfundimtare janë paraqitur në Fig. 8. Në shembull, vetëm rajoni veriperëndimor përfaqësohet në tabelën origjinale me më shumë se një regjistrim. Prandaj, në tabelën e rezultateve për të, funksione të ndryshme përmbledhëse japin vlera të ndryshme.

Oriz. 8. Tabela përmbledhëse e shumave të porosive sipas rajonit

Kur përdorni funksione përmbledhëse në listën e kolonave në deklaratën SELECT, titujt e kolonave përkatëse në tabelën e rezultateve janë Expr1001, Expr1002, etj. (ose diçka e ngjashme, në varësi të zbatimit të SQL). Sidoqoftë, ju mund të vendosni titujt për vlerat e funksioneve përmbledhëse dhe kolonave të tjera sipas dëshirës tuaj. Për ta bërë këtë, mjafton të specifikoni një shprehje të formës pas kolonës në deklaratën SELECT:

AS kollona_kollona

Fjala kyçe AS (as) do të thotë që në tabelën e rezultateve, kolona përkatëse duhet të ketë titullin e specifikuar pas AS. Titulli i caktuar quhet gjithashtu një pseudonim. Shembulli i mëposhtëm (Figura 9) vendos pseudonimet për të gjitha kolonat e llogaritura:

ZGJIDH rajonin,

SHUMË (Suma_order) AS [Shuma totale e porosisë],

AVG (Suma_order) AS [Shuma mesatare e porosisë],

MAX (Order_Sum) AS Maksimumi,

MIN (Order_Sum) SI Minimum,

NGA Klientët

GRUPI SIPAS Rajoni;

Oriz. 9. Tabela përmbledhëse e shumave të porosive sipas rajonit duke përdorur pseudonimet e kolonave

Pseudonimet me fjalë të shumta të ndara me hapësira janë të mbyllura në kllapa katrore.

Funksionet përmbledhëse mund të përdoren në klauzolat SELECT dhe HAVING, por ato nuk mund të përdoren në klauzolat WHERE. Një klauzolë HAVING është e ngjashme me klauzolën WHERE, por ndryshe nga klauzola WHERE ajo zgjedh rekordet në grupe.

Le të themi se dëshironi të përcaktoni se cilat rajone kanë më shumë se një klient. Për këtë qëllim, mund të përdorni kërkesën e mëposhtme:

ZGJIDH rajonin, numërimin (*)

NGA Klientët

GRUPI SIPAS Rajoni QË KA NUMËR (*)> 1;

Funksionet e trajtimit të vlerës

Kur punoni me të dhëna, shpesh duhet t'i përpunoni ato (ta konvertoni në formën e dëshiruar): zgjidhni një nënvarg në një varg, hiqni hapësirat kryesore dhe pasuese, rrumbullakosni një numër, llogaritni rrënjën katrore, përcaktoni kohën aktuale, etj. Ekzistojnë tre lloje funksionesh në SQL:

• funksionet e vargut;
• funksionet numerike;
• funksionet datë-kohë.

Funksionet e vargut

Funksionet e vargut marrin një varg si parametër dhe kthejnë një varg ose NULL pas përpunimit të tij.

• SUBSTRING (NGA fillimi i linjës)- kthen nënvargun që rezulton nga vargu i specifikuar si parametër linjë . Nënstring fillon me një karakter, numri rendor i të cilit është specifikuar në parametrin e fillimit dhe ka një gjatësi të specifikuar në parametrin gjatësi. Karakteret e rreshtit numërohen nga e majta në të djathtë, duke filluar nga 1. Kllapat katrore këtu tregojnë vetëm se shprehja e mbyllur në to është fakultative. Nëse shprehja PËR gjatësinë nuk përdoret, atëherë nënvargu nga Filloni deri në fund të rreshtit origjinal. Vlerat e parametrave fillimi dhe gjatësia duhet të zgjidhet në mënyrë që nënvargu i dëshiruar të jetë në të vërtetë brenda vargut origjinal. Përndryshe, funksioni SUBSTRING do të kthejë NULL.

Për shembull:

SUBSTRING ("E dashur Masha!" NGA 9 PËR 4) - kthen "Masha";

SUBSTRING ("E dashur Masha!" NGA 9) - kthen "Masha!";

SUBSTRING ("E dashur Masha!" NGA 15) -kthehet NULL.

Ju mund ta përdorni këtë funksion në një deklaratë SQL, për shembull, si kjo:

SELECT * NGA Klientët

WHERE SUBSTRING (Rajoni NGA 1 PËR 5) = "North";

• SIPER (varg ) - konverton të gjitha karakteret e vargut të specifikuar në shkronja të mëdha.
• LOWER (linja ) - konverton të gjitha karakteret e vargut të specifikuar në shkronja të vogla.
• TRIM (PREJTORËT | TRAILING | TË DYJA ["karakter"] NGA vargu ) - heq karakteret kryesore (LEADING), pasuese (TRAILING) ose të dy (DY) nga vargu. Karakteri i paracaktuar që do të fshihet është një hapësirë ​​(""), kështu që ju mund ta lini atë bosh. Ky funksion përdoret më shpesh për të hequr hapësirat.

Për shembull:

TRIM (QETON "" NGA "qyteti i Shën Petersburgut") rrotullohet "qyteti i Shën Petersburgut";

TRIM (TRALING "" NGA "qyteti i Shën Petersburgut") kthen "qytetin e Shën Petersburgut";

TRIM (TË DY "" NGA "qyteti i Shën Petersburgut") - kthen "qyteti i Shën Petersburgut";

TRIM (TË DY NGA "qyteti i Shën Petersburgut") - kthen "qyteti i Shën Petersburgut";

TRIM (TË DY "r" NGA "qyteti i Shën Petersburgut") - kthen "qyteti i Shën Petersburgut".

Ndër këto funksione, më të përdorurat janë SUBSTRING () dhe TRIM ().

Funksionet numerike

Funksionet numerike si parametër mund të pranojnë të dhëna jo vetëm të tipit numerik, por gjithmonë të kthejnë një numër ose NULL (vlerë e pacaktuar).

• POZICIONI ( String objektiv NË varg) - kërkon për një ndodhje të vargut të synuar në vargun e specifikuar. Nëse kërkimi është i suksesshëm, ai kthen numrin e pozicionit të karakterit të tij të parë, përndryshe - 0. Nëse vargu i synuar ka gjatësi zero (për shembull, vargu ""), atëherë funksioni kthen 1. Nëse të paktën një nga parametrat është NULL, pastaj kthehet NULL. Karakteret e rreshtit numërohen nga e majta në të djathtë, duke filluar nga 1.

Për shembull:

POZICIONI ("e" NË "Përshëndetje të gjithëve") - kthen 5;

POZICIONI ("të gjithë" NË "Përshëndetje të gjithëve") - kthen 8;

POZICIONI ("" Përshëndetje të gjithëve ") - kthen 1;

POZICIONI ("Përshëndetje!" NË "Përshëndetje të gjithëve") - Kthen 0.

Në tabelën Konsumatorët (shih Fig. 1), kolona Adresa përmban, përveç emrit të qytetit, kodin postar, emrin e rrugës dhe të dhëna të tjera. Mund t'ju duhet të zgjidhni të dhënat për klientët që jetojnë në një qytet të caktuar. Pra, nëse dëshironi të zgjidhni të dhënat që lidhen me klientët që jetojnë në Shën Petersburg, mund të përdorni shprehjen e mëposhtme të pyetjes SQL:

SELECT * NGA Klientët

KU POZICIONI ("Shën Petersburg" IN Adresa)> 0;

Vini re se kjo pyetje e thjeshtë për marrjen e të dhënave mund të formulohet ndryshe:

SELECT * NGA Klientët

KU Adresa LIKE "% Petersburg%";

• EKSTRAKT (parametër ) - nxjerr një artikull nga një vlerë datë-kohë ose nga një interval. Për shembull:

EKSTRAKT (MUAJ NGA DATA "2005-10-25") - kthen 10.

• CHARACTER_LENGTH (varg ) - kthen numrin e karaktereve në një varg.

Për shembull:

CHARACTER_LENGTH ("Përshëndetje të gjithëve") - Kthehet 11.

• OCTET_LENGTH (varg ) - kthen numrin e okteteve (bajt) në një varg. Çdo karakter latin ose cirilik përfaqësohet nga një bajt dhe një karakter kinez përfaqësohet nga dy bajt.
• KARDINALITETI (parametër ) - merr një koleksion artikujsh si parametër dhe kthen numrin e artikujve në koleksion (kardinal). Një koleksion mund të jetë, për shembull, një grup ose një grup i shumëfishtë që përmban elementë të llojeve të ndryshme.
• ABS (numri ) - kthen vlerën absolute të një numri. Për shembull:

ABS (-123) - kthen 123;

ABS (2 - 5) - Kthimet 3.

• MO D (numri 1, numri 2 ) - kthen pjesën e mbetur të pjesëtimit të numrit të plotë të numrit të parë me të dytin. Për shembull:

MOD (5, h) - kthen 2;

MOD (2, h) - Kthen 0.

• LN (numri ) - Kthen logaritmin natyror të një numri.
• EXP (numri) - kthen numrin e (baza e logaritmit natyror në fuqinë e numrit).
• POWER (numri 1, numri 2 ) - kthen numrin 1 numri 2 (numri 1 në fuqinë e numrit 2).
• SQRT (numri ) - Jep rrënjën katrore të një numri.
• KATI (numri ) - kthen numrin më të madh të plotë që nuk e kalon parametrin e specifikuar (rrumbullakimi poshtë). Për shembull:

KATI (5.123) - Kthen 5.0.

• TAVANI (numri) ose TAVANI (numri ) - kthen numrin më të vogël të plotë që nuk është më pak dhënë nga parametri rrumbullakimi). Për shembull:

CEIL (5.123) - Kthen 6.0.

• WIDTH_KOVË (numri 1, numri 2, numri 3, numri 4) kthen një numër të plotë në diapazonin ndërmjet 0 dhe numri 4 + 1. Parametrat numri2 dhe numri 3 specifikojnë një segment numerik të ndarë në intervale të barabarta, numri i të cilit përcaktohet nga parametri numër 4. Funksioni përcakton numrin e intervalit në të cilin vlera bie numri 1. Nëse numri 1 është jashtë diapazonit të specifikuar, atëherë funksioni kthen 0 ose numrin 4 + 1. Për shembull:

WIDTH_BUCKET (3.14, 0, 9, 5) - Kthimet 2.

Funksionet datë-kohë

Ekzistojnë tre funksione në SQL që kthejnë datën dhe kohën aktuale.

• CURRENT_DATA - kthen datën aktuale (lloji DATE).

Për shembull: 2005-06-18.

• CURRENT_TIME (numri ) - kthen kohën aktuale (lloji TIME). Një parametër numër i plotë specifikon saktësinë e sekondave. Për shembull, nëse vlera është 2, sekondat do të paraqiten me saktësi të njëqindtë (dy shifra në pjesën e pjesshme):

12:39:45.27.

• CURRENT_TIMESTAMP (numri ) - kthen datën dhe kohën (lloji TIMESTAMP). Për shembull, 2005-06-18 12: 39: 45.27. Një parametër numër i plotë specifikon saktësinë e sekondave.

Vini re se data dhe ora e kthyer nga këto funksione janë të tipit jo karakteresh. Nëse keni nevojë t'i përfaqësoni ato si vargje karakteresh, atëherë duhet të përdorni funksionin e konvertimit të tipit CAST ().

Funksionet e datës së kohës përdoren zakonisht në pyetje për të futur, përditësuar dhe fshirë të dhënat. Për shembull, kur regjistroni informacione rreth shitjeve, data dhe ora aktuale futen në një kolonë të dhënë posaçërisht. Pas përmbledhjes së totalit për muajin ose tremujorin, të dhënat e shitjeve për periudhën raportuese mund të fshihen.

Shprehjet e vlerësuara

Shprehjet e llogaritura ndërtohen nga konstante (numerike, vargje, logjike), funksione, emra fushash dhe lloje të tjera të dhënash duke i lidhur ato me aritmetikë, vargje, logjikë dhe operatorë të tjerë. Nga ana tjetër, shprehjet mund të kombinohen nga operatorët në shprehje më komplekse (të përbëra). Kllapat përdoren për të kontrolluar rendin në të cilin vlerësohen shprehjet.

Operatorët logjikë DHE, OSE dhe JO dhe funksionet janë diskutuar më herët.

Operatorët aritmetikë:

• + - shtim;
• - - zbritje;
• * - shumëzimi;
• / - ndarje.

Operatori i vargutvetëm njëri është operatori i lidhjes së vargut ose i lidhjes (| |). Ne disa Implementimet SQL(për shembull, Microsoft Access) në vend të (| |), përdoret simboli (+). Operatori i lidhjes shton rreshtin e dytë në fund të shembullit të parë, shprehje:

"Sasha" | | "dashuron" | | "Masha"

si rezultat do të kthejë vargun "Sasha e do Masha".

Kur kompozoni shprehje, duhet të siguroheni që operandët e operatorëve të kenë lloje të vlefshme. Për shembull, shprehja: 123 + "Sasha" është e pavlefshme sepse operatori i mbledhjes aritmetike zbatohet në operandin e vargut.

Shprehjet e vlerësuara mund të shfaqen pas deklaratës SELECT, si dhe në shprehjet e kushtëzuara të klauzolave ​​WHERE dhe HAVI NG.

Le të shohim disa shembuj.

Supozoni se tabela e Shitjeve përmban kolonat Lloji_Artikulli, Sasia dhe Çmimi, dhe ne duhet të dimë të ardhurat për çdo lloj produkti. Për ta bërë këtë, mjafton të përfshini shprehjen Sasia * Çmimi në listën e kolonave pas deklaratës SELECT:

SELECT_Lloji i artikullit, Sasia, Çmimi, Sasia * Çmimi AS

Totali NGA Shitjet;

Ai përdor fjalën kyçe AS (as) për të specifikuar një pseudonim për kolonën e të dhënave të llogaritura.

Në fig. 10 tregon tabelën origjinale të Shitjeve dhe tabelën e pyetjeve që rezulton.

Oriz. 10. Rezultati i një pyetësori me llogaritjen e të ardhurave për çdo lloj produkti

Nëse keni nevojë të zbuloni të ardhurat totale nga shitja e të gjitha mallrave, atëherë mjafton të aplikoni pyetjen e mëposhtme:

ZGJIDH SHUMEN (Sasia * Çmimi) NGA Shitjet;

Pyetja e mëposhtme përmban shprehje të vlerësuara si në listën e kolonave ashtu edhe në klauzolën WHERE. Ai zgjedh nga tabela e shitjeve ato mallra, të ardhurat nga shitja e të cilave janë më shumë se 1000:

SELECT_Lloji i artikullit, Sasia * Çmimi AS Total

NGA Shitjet

KU Sasia * Çmimi> 1000;

Supozoni se dëshironi të merrni një tabelë me dy kolona:

Produkti që përmban llojin dhe çmimin e produktit;

Totali që përmban të ardhura.

Meqenëse supozohet se në tabelën origjinale të shitjeve, kolona Item_type është karakter (i llojit CHAR), dhe kolona Çmimi është numerike, kur bashkoni (ngjitni) të dhënat nga këto kolona, ​​duhet të transferoni llojin numerik në karakter duke përdorur CAST () funksion. Pyetja që kryen këtë detyrë duket kështu (Fig. 11):

ZGJIDH Llojin_Artikulli | | "(Çmimi:" | | CAST (Çmimi AS CHAR (5)) | | ")" AS Produkt, Sasia * Çmimi AS Total

NGA Shitjet;

Oriz. 11. Rezultati i një pyetësori me kombinimin e llojeve të ndryshme të të dhënave në një kolonë

Shënim. Në Microsoft Access, një pyetje e ngjashme do të duket si kjo:

SELECT Lloji_Artikulli + "(Çmimi:" + C Rr (Çmimi) + ")" AS Produkt,

Sasia * Çmimi AS Total

NGA Shitjet;

Shprehjet e kushtëzuara me Deklaratë RAST

Në gjuhët e zakonshme të programimit ekzistojnë operatorë kërcimi të kushtëzuar që ju lejojnë të kontrolloni procesin llogaritës në varësi të faktit nëse një kusht i caktuar plotësohet apo jo. Në SQL, një deklaratë e tillë është CASE (rast, rrethanë, shembull). Në SQL: 2003, ky operator kthen një vlerë dhe për këtë arsye mund të përdoret në shprehje. Ai ka dy forma kryesore, të cilat do t'i shikojmë në këtë seksion.

Deklarata RASTE me vlera

Deklarata CASE me vlera ka sintaksën e mëposhtme:

CASE check_value

KUR vlera 1 PASTAJ rezulton 1

KUR vlera 2 PASTAJ rezulton 2

. . .

KUR vlera N atherë rezulton N

TJETER rezultati X

Në rastin kur kontrolluar_vlera e barabartë me vlerën 1 , deklarata CASE kthen vlerën rezultat 1 specifikuar pas fjalës kyçe THEN (pastaj). Përndryshe, checked_value krahasohet me vlera 2 , dhe nëse janë të barabarta, atëherë kthehet rezultati2. Përndryshe, vlera e kontrolluar krahasohet me vlerën tjetër të specifikuar pas fjalës kyçe WHEN (kur) dhe kështu me radhë. Nëse checked_value nuk është e barabartë me asnjërën nga këto vlera, atëherë vlera kthehet rezultati X specifikuar pas fjalës kyçe ELSE (ndryshe).

Fjala kyçe ELSE është fakultative. Nëse mungon dhe asnjë nga vlerat që do të krahasohen nuk është e barabartë me vlerën e testuar, atëherë deklarata CASE kthen NULL.

Supozoni, bazuar në tabelën Customers (shih Fig. 1), ju dëshironi të merrni një tabelë në të cilën emrat e rajoneve zëvendësohen me numrat e tyre të kodit. Nëse nuk ka shumë rajone të ndryshme në tabelën origjinale, atëherë për të zgjidhur këtë problem është i përshtatshëm të përdorni një pyetje me operatorin CASE:

ZGJIDHni emrin, adresën,

Rajoni i RASTIT

KUR "Moska" APO "77"

KUR "Rajoni i Tverit" APO "69"

. . .

Rajoni TJETËR

Kodi i Rajonit AS

NGA Klientët;

Deklarata CASE me termat e kërkimit

Forma e dytë e operatorit CASE supozon përdorimin e saj kur kërkon në një tabelë ato regjistrime që plotësojnë një kusht të caktuar:

RAST

KUR kushti 1 ATHE rezultoi 1

KUR rezultojnë 2 ATHE rezultojnë 2

. . .

KUR kushti N ATHE rezultoi N

TJETER rezultati X

Deklarata CASE kontrollon nëse kushti 1 është i vërtetë për regjistrimin e parë në grupin e specifikuar nga klauzola WHERE, ose në të gjithë tabelën nëse nuk ka WHERE. Nëse po, CASE kthen rezultatin1. Përndryshe, kushti 2 kontrollohet për këtë regjistrim. Nëse është e vërtetë, atëherë kthehet vlera result2, e kështu me radhë.Nëse asnjë nga kushtet nuk plotësohet, atëherë vlera kthehet. X specifikuar pas fjalës kyçe ELSE.

Fjala kyçe ELSE është fakultative. Nëse mungon dhe nuk plotësohet asnjë nga kushtet, deklarata CASE rrotullohet NULL. Pasi deklarata që përmban CASE është ekzekutuar për regjistrimin e parë, ai kalon në regjistrimin tjetër. Kjo vazhdon derisa të përpunohet i gjithë grupi i regjistrimeve.

Për shembull, në tabelën e librit (Titulli, Çmimi), kolona është NULL nëse libri përkatës nuk është i disponueshëm. Kërkesa e mëposhtme kthen një tabelë që shfaq tekstin "Jo i disponueshëm" në vend të NULL:

ZGJIDH emrin,

RAST

KUR QMIMI ËSHTË NULL atherë "Jashtë stok"

CAST TJETËR (Çmimi SI KARINO (8))

SI Çmimi

NGA Librat;

Të gjitha vlerat në të njëjtën kolonë duhet të jenë të të njëjtave lloje. Prandaj, në këtë kërkesë funksioni i konvertimit të tipit CAST përdoret për të hedhur vlerat numerike të kolonës së Çmimit në një lloj karakteri.

Vini re se në vend të formës së parë të deklaratës CASE, gjithmonë mund të përdorni të dytën:

RAST

KUR test_vlera = vlera 1 PASTAJ rezultati1

KUR test_vlera = vlera2 PASTAJ rezultati2

. . .

KUR checked_value = vlerë N PASTAJ rezultatN

TJETER rezultatiX

Funksionet NULLIF dhe COALESCE

Në disa raste, veçanërisht në pyetjet për përditësimin e të dhënave (deklarata UPDATE), është e përshtatshme të përdoren funksionet më kompakte NULLIF () (NULL nëse) dhe COALESCE () (kombinim) në vend të deklaratës së rëndë CASE.

Funksioni NULLIF ( vlera 1, vlera 2) kthen NULL nëse vlera e parametrit të parë përputhet me vlerën e parametrit të dytë, nëse nuk përputhet, vlera e parametrit të parë kthehet e pandryshuar. Kjo do të thotë, nëse plotësohet vlera e barazisë1 = vlera2, atëherë funksioni kthen NULL, përndryshe - vlerën e vlerës1.

Ky funksion është ekuivalent me deklaratën CASE në dy format e mëposhtme:

• Vlera CASE1

WHEN vlera 2 PASTAJ NULL

TJETER vlera 1

• RAST

KUR vlera 1 = vlera 2 PASTAJ NULL

TJETER vlera 1

Funksioni COALESCE ( vlera 1, vlera 2, ..., vlera N) pranon një listë vlerash, të cilat mund të jenë të përcaktuara ose të pavlefshme. Funksioni kthen një vlerë specifike nga lista, ose NULL nëse të gjitha vlerat janë të papërcaktuara.

Ky funksion është ekuivalent me deklaratën e mëposhtme CASE:

RAST

KUR vlera 1 NUK ËSHTË NULL ATËHERË vlera 1

KUR vlera 2 NUK ËSHTË NULL ATHERE vlera 2

. . .

KUR vlera N NUK ËSHTË NULL ATËHERË vlera N

TJETER NULL

Për shembull, në tabelën Librat (Titulli, Çmimi), kolona Çmimi është NULL nëse libri përkatës nuk është i disponueshëm. Pyetja e mëposhtme kthen një tabelë në të cilën, në vend të I PAVLEFSHËM shfaqet teksti "Jo i disponueshëm":

ZGJIDH Emrin, BASHKIMI (CAST (Çmimi AS CHAR (8)),

"Jashtë stok") AS Çmimi

NGA Librat;

Mësimi SQL 11. Funksionet përmbledhëse, kolonat e llogaritura dhe pamjet

• Funksioni AVG () kthen vlerën mesatare të një kolone.


• COUNT () Funksioni kthen numrin e rreshtave në një kolonë.


• MAX () Funksioni kthen më shumë rëndësi të madhe në kolonë.


• MIN () Funksioni kthen vlerën më të vogël në kolonë.


• Funksioni SUM () kthen shumën e vlerave të kolonës.

SELECT MIN (çmimi), MAX (çmimi), AVG (çmimi) FROM çmimet;

Tani, ne duam të zbulojmë se sa na i ka sjellë furnizuesi "Shtypshkronja" (id = 2). Nuk është aq e lehtë të bësh një kërkesë të tillë. Le të mendojmë se si ta kompozojmë atë:

1. Së pari, nga tabela Deliveries (incoming), zgjidhni identifikuesit (id_incoming) të atyre dërgesave që janë bërë nga furnizuesi i "Shtypshkronjës" (id = 2):

2. Tani nga tabela Revista e dërgesave (magazine_incoming) ju duhet të zgjidhni mallrat (id_produkti) dhe sasitë e tyre (sasia), të cilat janë kryer në dërgesat e gjetura në paragrafin 1. Kjo do të thotë, kërkesa nga paragrafi 1 bëhet e ndërthurur:

3. Tani duhet të shtojmë në tabelën që rezulton çmimet për produktet e gjetura, të cilat ruhen në tabelën e çmimeve. Kjo do të thotë, ne duhet të bashkojmë tabelat Magazine_incoming dhe Prices nga kolona id_product:

4. Tabela që rezulton i mungon qartë kolona Sum, d.m.th kolona e llogaritur... Mundësia për të krijuar kolona të tilla ofrohet në MySQL. Për ta bërë këtë, thjesht duhet të specifikoni në pyetje emrin e kolonës së llogaritur dhe çfarë duhet të llogarisë. Në shembullin tonë, një kolonë e tillë do të quhet përmbledhje dhe do të llogarisë produktin e kolonave të sasisë dhe çmimit. Emri i kolonës së re ndahet me fjalën AS:

SELECT magazine_incoming.id_product, magazine_incoming.sasia, Prices.price, magazine_incoming.sasia * Prices.price AS përmbledhje NGA magazine_incoming, çmimet WHERE magazine_incoming.id_produkt = çmimet.id_produkti DHE SELECT_produkti RE_incoming

5. E shkëlqyeshme, gjithçka që duhet të bëjmë është të përmbledhim kolonën përmbledhëse dhe më në fund të zbulojmë se sa na i ka sjellë furnizuesi "Shtypshkronja" mallrat. Sintaksa për përdorimin e funksionit SUM () është si më poshtë:

SELECT SUM (emri_kolona) NGA emri i tabeles;

Ne e dimë emrin e kolonës - summa, por nuk e kemi emrin e tabelës, pasi është rezultat i pyetjes. Çfarë duhet bërë? Për raste të tilla, MySQL ofron Views. Një pamje është një pyetje e zgjedhur që i është caktuar një emër unik dhe mund të ruhet në një bazë të dhënash për përdorim të mëvonshëm.

Sintaksa për krijimin e një pamjeje është si më poshtë:

KRIJO PAMJE Emri_view SI pyetësor;

Le ta ruajmë kërkesën tonë si një pamje me emrin report_vendor:

KRIJO PAMJE report_vendor SI ZGJEDHJE magazine_incoming.id_product, magazine_incoming.sasia, Prices.price, Magazine_incoming.sasia * Prices.price AS përmbledhje NGA revista_arritëse, çmimet WHERE magazine_incoming.id_product =ME magazine_incoming.id_produkti =Në ME RE_incoming.id_produkt =nëFRED_CT_në_produkti_në________në_______çmimet. ;

6. Tani mund të përdorni funksionin përfundimtar SUM ():

SELECT SUM (përmbledhje) NGA raporti_shitësi;

Pra, ne kemi arritur rezultatin, edhe pse për këtë na është dashur të përdorim pyetje të ndërthurura, bashkime, kolona të llogaritura dhe pamje. Po, ndonjëherë duhet të mendosh për të marrë rezultatin, pa atë askund. Por ne prekëm dy tema shumë të rëndësishme - kolonat e llogaritura dhe pamjet. Le të flasim për to në më shumë detaje.

Fushat e llogaritura (kolonat)

SELECT kolona_1_emri, kolona_2_emri, kolona_1_emri * kolona_2_emri AS llogaritur_emri_kolones FROM_emri_tabeles;

Nuanca e dytë është fjala kyçe AS, e kemi përdorur për të vendosur emrin e kolonës së llogaritur. Në fakt, kjo fjalë kyçe përdoret për të vendosur pseudonime për çdo kolonë. Pse është e nevojshme kjo? Për kod më të shkurtër dhe më të lexueshëm. Për shembull, pamja jonë mund të duket si kjo:

KRIJO PAMJE report_vendor SI ZGJIDH A.id_product, A.sasi, B.price, A.sasi * B.çmimi AS përmbledhje NGA revista_incoming AS A, çmimet AS B KU A.id_product = B.id_product DHE id_incoming = (SELECT id_incoming FROM WHERE id_shitësi = 2);

Pajtohuni që kjo është shumë më e shkurtër dhe më e qartë.

Përfaqësimi

Sidoqoftë, mbani në mend se pamjet nuk janë tabela, domethënë ato nuk ruajnë të dhëna, ato i marrin vetëm nga tabelat e tjera. Prandaj, së pari, kur të dhënat në tabela ndryshojnë, do të ndryshojnë edhe rezultatet e prezantimit. Dhe së dyti, kur i bëhet një pyetje një pamjeje, kërkohen të dhënat e kërkuara, domethënë, performanca e DBMS zvogëlohet. Prandaj, ato nuk duhet të abuzohen.

Ky tutorial do t'ju tregojë se si ta përdorni funksioni SUM v SQL Server(Transact-SQL) me sintaksë dhe shembuj.

Përshkrim

Serveri SQL (Transact-SQL) Funksioni SUM kthen vlerën totale të shprehjes.

Sintaksë

Sintaksa për funksionin SUM në SQL Server (Transact-SQL) është:

OSE sintaksa për funksionin SUM kur grupohen rezultatet sipas një ose më shumë kolonave është:

Parametrat ose argumentet

shprehja1, shprehja2, ... shprehja_n janë shprehje që nuk përfshihen në funksionin SUM dhe duhet të përfshihen në klauzolën GROUP BY në fund të deklaratës SQL.
agregate_expression është kolona ose shprehja që do të përmblidhet.
tabela - tabelat nga të cilat dëshironi të merrni të dhënat. Duhet të ketë të paktën një tabelë të listuar në klauzolën FROM.
KU kushtet janë fakultative. Këto janë kushtet që duhet të plotësohen për të dhënat e përzgjedhura.

Aplikacion

Funksioni SUM mund të përdoret në versionet e mëposhtme të SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Shembull me një fushë të vetme

Le të shohim disa shembuj të funksionit SUM Server SQL për të kuptuar se si të përdoret funksioni SUM në SQL Server (Transact-SQL).

Për shembull, mund të zbuloni se si totali i të gjitha produkteve, numri i të cilave është më i madh se 10.

Në këtë shembull të funksionit SUM, ne vendosëm shprehjen SUM (sasi) në pseudonimin "Sasia totale". Kur ktheni një grup rezultatesh - "Sasia totale" do të shfaqet si emri i fushës.

Një shembull i përdorimit të DISTINCT

Mund të përdorni operatorin DISTINCT në funksionin SUM. Për shembull të dhënë më poshtë Deklarata SQL kthen pagën totale me vlera unike pagash, ku paga është më pak se 29,000 dollarë në vit.

Nëse dy paga do të ishin 24,000 dollarë në vit, vetëm një nga këto vlera do të përdorej në funksionin SUM.

Shembull i përdorimit të një formule

Shprehja e përfshirë në funksionin SUM nuk duhet të jetë një fushë e vetme. Ju gjithashtu mund të përdorni një formulë. Për shembull, ju mund të llogarisni komisionin total.

Transact-SQL

SELECT SUM (shitje * 0.03) SI "Komisioni Total" NGA porositë;

Shembull i përdorimit të GROUP BY

Në disa raste, do t'ju duhet të përdorni klauzolën GROUP BY me funksionin SUM.

Si mund të zbuloj numrin e modeleve të PC-ve të prodhuara nga një shitës i caktuar? Si të përcaktohet çmimi mesatar për kompjuterët që kanë të njëjtën specifikimet? Këto dhe shumë pyetje të tjera që lidhen me disa informacione statistikore mund të përgjigjen duke përdorur funksionet përmbledhëse (agregate).... Standardi parashikon funksionet e mëposhtme agregate:

Të gjitha këto funksione kthejnë një vlerë të vetme. Në këtë rast, funksionet COUNT, MIN dhe MAX janë të zbatueshme për çdo lloj të dhënash, ndërsa SHUMË dhe AVG përdoren vetëm për fushat numerike. Dallimi midis funksionit COUNT (*) dhe COUNT (<имя поля>) është se e dyta nuk merr parasysh vlerat NULL gjatë llogaritjes.

Shembull. Gjeni çmimin minimal dhe maksimal për kompjuterët personalë:

Shembull. Gjeni numrin e kompjuterëve të disponueshëm nga prodhuesi A:

Shembull. Nëse jemi të interesuar për numrin e modeleve të ndryshme të prodhuara nga prodhuesi A, atëherë pyetja mund të formulohet si më poshtë (duke përdorur faktin se në tabelën e produktit çdo model regjistrohet një herë):

Shembull. Gjeni numrin e modeleve të ndryshme të disponueshme nga prodhuesi A. Pyetja është e ngjashme me atë të mëparshme, në të cilën kërkohej të përcaktohej numri total i modeleve të prodhuara nga prodhuesi A. Këtu duhet të gjeni edhe numrin e modeleve të ndryshme në Tavolinë PC (dmth në dispozicion për shitje).

Për të siguruar që përdoren vetëm vlera unike kur merren tregues statistikorë, kur argumenti i funksioneve agregate mund të përdoret Parametri DISTINCT... Një tjetër parametri ALLështë parazgjedhja dhe supozon që të gjitha vlerat e kthyera në kolonë janë numëruar. Operator,

Nëse duhet të marrim numrin e modeleve të PC-ve të prodhuara çdo prodhuesi, do t'ju duhet të përdorni GRUPI PËR klauzolë në vijim sintaksor KU klauzolat.

GRUPI PËR klauzolë

Nëse SELECT kishte një kolonë me një datë, atëherë do të ishte e mundur të llogariteshin këta tregues për çdo datë specifike. Për ta bërë këtë, duhet të shtoni një datë si një kolonë grupimi, dhe më pas do të llogariten funksionet e përmbledhura për çdo kombinim vlerash (model-datë).

Ka disa specifike rregullat për kryerjen e funksioneve agregate:

• Nëse si rezultat i pyetjes asnjë rresht nuk është marrë(ose më shumë se një rresht për një grup të caktuar), atëherë mungojnë të dhënat fillestare për llogaritjen e ndonjë prej funksioneve agregate. Në këtë rast, rezultati i ekzekutimit të funksioneve COUNT do të jetë zero, dhe rezultati i të gjitha funksioneve të tjera do të jetë NULL.
• Argumenti funksioni agregat vetë nuk mund të përmbajë funksione agregate(funksioni nga funksioni). ato. në një pyetje të vetme, ju nuk mund, të themi, të merrni maksimumin e mesatareve.
• Rezultati i ekzekutimit të funksionit COUNT është numër i plotë(SHUMË I PLOTË). Funksionet e tjera agregate trashëgojnë llojet e të dhënave të vlerave që përpunohen.
• Nëse, gjatë ekzekutimit të funksionit SUM, është marrë një rezultat që tejkalon vlerën maksimale të llojit të të dhënave të përdorur, gabim.

Pra, nëse kërkesa nuk përmban GRUP SIPAS klauzolave, pastaj funksionet agregate të përfshira në Klauzola SELECT, ekzekutohen në të gjitha linjat e pyetjeve që rezultojnë. Nëse kërkesa përmban GRUPI PËR klauzolë, çdo grup rreshtash që ka të njëjtat vlera të grupit të kolonave ose kolonave të specifikuara në GRUPI PËR klauzolë, përbën një grup, dhe funksionet agregate kryhen për secilin grup veç e veç.

Klauzola e PASJES

Nëse Klauzola WHERE përcakton një kallëzues për filtrimin e rreshtave, më pas Duke pasur ofertë aplikuar pas grupimit për të përcaktuar një grup të ngjashëm filtrues kallëzues sipas vlerave funksionet agregate... Kjo klauzolë është e nevojshme për të testuar vlerat që janë marrë duke përdorur funksioni agregat jo nga linja të veçanta të burimit të regjistrimit të përcaktuar në Klauzola NGA, dhe nga grupe të linjave të tilla... Prandaj, një kontroll i tillë nuk mund të përfshihet Klauzola WHERE.