Ako zistím počet modelov PC vyrobených konkrétnym predajcom? Ako určiť priemernú hodnotu ceny počítačov s rovnakými technickými vlastnosťami? Tieto a mnohé ďalšie otázky súvisiace s niektorými štatistickými informáciami je možné zodpovedať pomocou súhrnné (súhrnné) funkcie. Norma poskytuje nasledujúce agregačné funkcie:

Všetky tieto funkcie vracajú jednu hodnotu. Zároveň funkcie COUNT, MIN a MAX použiť na akýkoľvek typ údajov, zatiaľ čo SUM a AVG používa sa len pre číselné polia. Rozdiel medzi funkciami COUNT(*) a COUNT(<имя поля>) je, že druhá nezohľadňuje pri výpočte hodnoty NULL.

Príklad. Nájdite minimálnu a maximálnu cenu osobných počítačov:

Príklad. Nájdite dostupný počet počítačov vyrobených výrobcom A:

Príklad. Ak nás zaujíma počet rôznych modelov vyrobených výrobcom A, potom môže byť dotaz formulovaný nasledovne (s využitím skutočnosti, že každý model je zaznamenaný raz v tabuľke produktov):

Príklad. Zistite počet dostupných rôznych modelov vyrobených výrobcom A. Dotaz je podobný predchádzajúcemu, v ktorom bolo potrebné zistiť celkový počet modelov vyrobených výrobcom A. Tu je potrebné zistiť počet rôznych modelov v PC tabuľke (t. j. dostupné na predaj).

Aby sa zabezpečilo, že pri získavaní štatistických ukazovateľov sa používajú iba jedinečné hodnoty, keď argument agregovanej funkcie môže byť použité DISTINCT parameter. Ďalší parameter ALL je predvolená a očakáva, že sa spočítajú všetky návratové hodnoty v stĺpci. operátor,

Ak potrebujeme získať počet vyrobených modelov PC o každý výrobcu, budete musieť použiť Ponuka GROUP BY, syntakticky nasleduje po klauzuly WHERE.

Ponuka GROUP BY

Ponuka GROUP BY sa používa na definovanie skupín výstupných riadkov, na ktoré možno použiť súhrnné funkcie (COUNT, MIN, MAX, AVG a SUM). Ak táto klauzula chýba a sú použité agregačné funkcie, potom všetky stĺpce s názvami uvedenými v VYBRAŤ, by mali byť zahrnuté v agregované funkcie a tieto funkcie sa použijú na celú množinu riadkov, ktoré spĺňajú predikát dotazu. V opačnom prípade všetky stĺpce zoznamu SELECT, nezahŕňa do agregovaných funkcií, musia byť špecifikované v klauzule GROUP BY. Výsledkom je, že všetky výstupné riadky dotazu sú rozdelené do skupín charakterizovaných rovnakými kombináciami hodnôt v týchto stĺpcoch. Potom sa agregované funkcie aplikujú na každú skupinu. Všimnite si, že pre GROUP BY sa všetky hodnoty NULL považujú za rovnaké, t.j. pri zoskupení podľa poľa obsahujúceho hodnoty NULL budú všetky takéto riadky spadať do jednej skupiny.
Ak ak existuje klauzula GROUP BY, v klauzule SELECT žiadne agregované funkcie, potom dotaz jednoducho vráti jeden riadok z každej skupiny. Túto funkciu spolu s kľúčovým slovom DISTINCT možno použiť na odstránenie duplicitných riadkov v množine výsledkov.
Zvážte jednoduchý príklad:

SELECT model, COUNT(model) AS Množstvo_model, AVG(cena) AS Priemerná_cena Z PC GROUP BY model;

V tomto dotaze sa pre každý model PC určí ich počet a priemerná cena. Všetky riadky s rovnakou hodnotou modelu (číslo modelu) tvoria skupinu a výstup SELECT vypočíta počet hodnôt a priemerné hodnoty ceny pre každú skupinu. Výsledkom dotazu bude nasledujúca tabuľka:

Model	Model_množstva	Priemerná_cena
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

Ak by bol vo SELECTe stĺpec s dátumom, potom by bolo možné vypočítať tieto ukazovatele pre každý konkrétny dátum. Ak to chcete urobiť, musíte pridať dátum ako stĺpec zoskupenia a potom by sa agregované funkcie vypočítali pre každú kombináciu hodnôt (dátum modelu).

Špecifických je viacero pravidlá pre vykonávanie agregovaných funkcií:

• Ak v dôsledku žiadosti neprišiel žiadny riadok(alebo viac ako jeden riadok pre túto skupinu), potom neexistujú žiadne počiatočné údaje na výpočet žiadnej zo súhrnných funkcií. V tomto prípade bude výsledok vykonania funkcií COUNT nula a výsledok všetkých ostatných funkcií bude NULL.
• Argumentovať agregátna funkcia nemôže obsahovať agregované funkcie(funkcia z funkcie). Tie. v jednej žiadosti nie je možné, povedzme, dostať maximálne priemerné hodnoty.
• Výsledkom vykonania funkcie COUNT je celé číslo(INTEGER). Ostatné agregačné funkcie dedia dátové typy spracovaných hodnôt.
• Ak sa pri vykonávaní funkcie SUM získal výsledok, ktorý prekročil maximálnu hodnotu použitého typu údajov, chyba.

Ak teda žiadosť neobsahuje GROUP BY ponuky, potom agregované funkcie zahrnuté v klauzula SELECT, sú vykonávané na všetkých výsledných reťazcoch dopytov. Ak žiadosť obsahuje Ponuka GROUP BY, každá sada riadkov, ktorá má rovnaké hodnoty stĺpca alebo skupiny stĺpcov špecifikovaných v ponuka GROUP BY, tvorí skupinu a agregované funkcie vykonávané pre každú skupinu samostatne.

MÁME ponuku

Ak klauzula WHERE potom definuje predikát pre filtrovanie reťazcov klauzula HAVING aplikovaný po zoskupení na definovanie podobných predikátových skupín filtrovania podľa hodnôt agregované funkcie. Táto klauzula je potrebná na overenie hodnôt, ktoré sa získajú agregátna funkcia nie zo samostatných riadkov zdroja záznamov definovaných v doložka FROM, a od skupiny takýchto liniek. Preto takáto kontrola nemôže byť obsiahnutá klauzula WHERE.

Nasledujúce podsekcie popisujú ďalšie klauzuly príkazu SELECT, ktoré možno použiť v dotazoch, ako aj súhrnné funkcie a sady príkazov. Pre pripomenutie, doteraz sme sa zaoberali použitím klauzuly WHERE a v tomto článku sa pozrieme na klauzuly GROUP BY, ORDER BY a HAVING a uvedieme niekoľko príkladov použitia týchto klauzúl v spojení s agregovaným funkcie, ktoré sú podporované v Transact-SQL.

Ponuka GROUP BY

Veta GROUP BY zoskupí vybranú množinu riadkov a vytvorí množinu súhrnných riadkov na základe hodnôt jedného alebo viacerých stĺpcov alebo výrazov. Jednoduché použitie klauzuly GROUP BY je uvedené v príklade nižšie:

USE SampleDb; SELECT Job FROM Works_On GROUP BY Job;

V tomto príklade sú vybrané a zoskupené pozície zamestnancov.

Vo vyššie uvedenom príklade klauzula GROUP BY vytvára samostatnú skupinu pre všetky možné hodnoty (vrátane NULL) stĺpca Job.

Použitie stĺpcov v klauzule GROUP BY musí spĺňať určité podmienky. Najmä každý stĺpec vo výberovom zozname dotazu sa musí objaviť aj v klauzule GROUP BY. Táto požiadavka sa nevzťahuje na konštanty a stĺpce, ktoré sú súčasťou súhrnnej funkcie. (Agregované funkcie sú diskutované v ďalšej podkapitole.) Dáva to zmysel, pretože iba stĺpcom v klauzule GROUP BY je garantovaná jedna hodnota na skupinu.

Tabuľku možno zoskupiť podľa ľubovoľnej kombinácie jej stĺpcov. Príklad nižšie ukazuje zoskupenie riadkov tabuľky Works_on do dvoch stĺpcov:

USE SampleDb; SELECT ProjectNumber, Job FROM Works_On GROUP BY ProjectNumber, Job;

Výsledkom tohto dotazu je:

Na základe výsledkov dotazu môžete vidieť, že existuje deväť skupín s rôznymi kombináciami čísla projektu a pozície. Poradie názvov stĺpcov v klauzule GROUP BY nemusí byť rovnaké ako v zozname stĺpcov SELECT.

Súhrnné funkcie

Na získanie celkových hodnôt sa používajú súhrnné funkcie. Všetky agregované funkcie možno rozdeliť do nasledujúcich kategórií:

bežné agregované funkcie;

štatistické agregované funkcie;

agregované funkcie definované používateľom;

analytické agregačné funkcie.

Tu sa pozrieme na prvé tri typy agregovaných funkcií.

Bežné súhrnné funkcie

Jazyk Transact-SQL podporuje nasledujúcich šesť agregovaných funkcií: MIN, MAX, SUM, AVG, COUNT, COUNT_BIG.

Všetky agregačné funkcie vykonávajú výpočty s jedným argumentom, ktorým môže byť stĺpec alebo výraz. (Jediná výnimka je druhá forma dvoch funkcií COUNT a COUNT_BIG, konkrétne COUNT(*) a COUNT_BIG(*) v tomto poradí.) Výsledkom akejkoľvek agregovanej funkcie je konštantná hodnota zobrazená v samostatnom stĺpci výsledkov.

Agregačné funkcie sú špecifikované v zozname stĺpcov príkazu SELECT, ktorý môže obsahovať aj klauzulu GROUP BY. Ak v príkaze SELECT nie je klauzula GROUP BY a zoznam stĺpcov select obsahuje aspoň jednu agregovanú funkciu, potom nesmie obsahovať jednoduché stĺpce (iné ako stĺpce, ktoré slúžia ako argumenty agregačnej funkcie). Preto je kód v nižšie uvedenom príklade nesprávny:

USE SampleDb; SELECT Priezvisko, MIN(Id) FROM Zamestnanec;

Tu by stĺpec Priezvisko tabuľky Zamestnanec nemal byť v zozname na výber stĺpcov, pretože nejde o argument agregovanej funkcie. Na druhej strane zoznam na výber stĺpcov môže obsahovať názvy stĺpcov, ktoré nie sú argumentmi pre agregovanú funkciu, ak sú tieto stĺpce argumentmi klauzuly GROUP BY.

Argumentu agregovanej funkcie môže predchádzať jedno z dvoch možných kľúčových slov:

VŠETKY

Určuje, že výpočty sa vykonávajú so všetkými hodnotami v stĺpci. Toto je predvolená hodnota.

ODLIŠNÝ

Určuje, že na výpočty sa používajú iba jedinečné hodnoty stĺpcov.

Súhrnné funkcie MIN a MAX

Agregačné funkcie MIN a MAX vypočítajú najmenšiu a najväčšiu hodnotu stĺpca. Ak dotaz obsahuje klauzulu WHERE, funkcie MIN a MAX vrátia najmenšiu a najväčšiu hodnotu riadkov, ktoré spĺňajú zadané podmienky. Nasledujúci príklad ukazuje použitie agregačnej funkcie MIN:

USE SampleDb; -- Vráti 2581 SELECT MIN(Id) AS "Min Id" OD zamestnanca;

Výsledok vrátený v príklade vyššie nie je veľmi informatívny. Napríklad nie je známe meno zamestnanca, ktorý toto číslo vlastní. Toto priezvisko však nie je možné získať bežným spôsobom, pretože, ako už bolo spomenuté, nie je povolené explicitne špecifikovať stĺpec Priezvisko. Na získanie priezviska tohto zamestnanca spolu s najnižším osobným číslom zamestnanca sa používa poddotaz. Nasledujúci príklad ukazuje použitie takéhoto poddotazu, kde poddotaz obsahuje príkaz SELECT z predchádzajúceho príkladu:

Výsledok vykonania dotazu:

Použitie agregačnej funkcie MAX je znázornené v príklade nižšie:

Funkcie MIN a MAX môžu tiež brať reťazce a dátumy ako argumenty. V prípade argumentu reťazca sa hodnoty porovnávajú pomocou skutočného poradia zoradenia. Pre všetky argumenty dočasného dátumu je najmenšia hodnota stĺpca najskorší dátum a hodnota najväčšieho stĺpca je najnovší dátum.

Kľúčové slovo DISTINCT môžete použiť s funkciami MIN a MAX. Pred použitím agregačných funkcií MIN a MAX sú všetky hodnoty NULL vylúčené z ich stĺpcov argumentov.

Súhrnná funkcia SUM

Agregát Funkcia SUM vypočíta celkový súčet hodnôt stĺpca. Argument tejto agregačnej funkcie musí byť vždy typu číselných údajov. Použitie agregačnej funkcie SUM je znázornené v príklade nižšie:

USE SampleDb; SELECT SUM (Rozpočet) "Súhrnný rozpočet" FROM Project;

Tento príklad vypočíta celkový súčet rozpočtov všetkých projektov. Výsledok vykonania dotazu:

V tomto príklade agregovaná funkcia zoskupuje všetky hodnoty rozpočtu projektu a určuje ich celkovú sumu. Z tohto dôvodu dotaz obsahuje implicitnú funkciu zoskupovania (ako všetky podobné dotazy). Funkciu implicitného zoskupovania z vyššie uvedeného príkladu možno špecifikovať explicitne, ako je uvedené v príklade nižšie:

USE SampleDb; SELECT SUM (Rozpočet) "Celkový rozpočet" FROM Project GROUP BY();

Použitie možnosti DISTINCT eliminuje všetky duplicitné hodnoty v stĺpci pred použitím funkcie SUM. Podobne sa pred použitím tejto agregačnej funkcie odstránia všetky hodnoty NULL.

Agregátna funkcia AVG

Agregát Funkcia AVG vráti aritmetický priemer všetkých hodnôt v stĺpci. Argument tejto agregačnej funkcie musí byť vždy typu číselných údajov. Pred použitím funkcie AVG sa z jej argumentu odstránia všetky hodnoty NULL.

Použitie agregačnej funkcie AVG je znázornené v príklade nižšie:

USE SampleDb; -- Vráti 133833 SELECT AVG (Rozpočet) "Priemerný rozpočet na projekt" FROM Project;

Tu sa vypočítava aritmetický priemer rozpočtu pre všetky rozpočty.

Súhrnné funkcie COUNT a COUNT_BIG

Agregát Funkcia COUNT má dve rôzne formy:

COUNT( názov_stĺpca) COUNT(*)

Prvá forma funkcie počíta počet hodnôt v stĺpci col_name. Ak sa v dotaze použije kľúčové slovo DISTINCT, pred použitím funkcie COUNT sa odstránia všetky duplicitné hodnoty stĺpcov. Táto forma funkcie COUNT neberie do úvahy hodnoty NULL pri počítaní počtu hodnôt v stĺpci.

Použitie prvého tvaru agregačnej funkcie COUNT je znázornené v príklade nižšie:

USE SampleDb; SELECT ProjectNumber, COUNT(DISTINCT Job) "Práca v projekte" FROM Works_on GROUP BY ProjectNumber;

Tu sa počíta počet rôznych pozícií pre každý projekt. Výsledkom tohto dotazu je:

Ako môžete vidieť z príkladu dotazu, funkcia COUNT nezohľadnila hodnoty NULL. (Súčet všetkých hodnôt v stĺpci úlohy sa ukázal ako 7, nie 11, ako by mal byť.)

Druhá forma funkcie COUNT, t.j. funkcia COUNT(*) počíta počet riadkov v tabuľke. A ak príkaz SELECT dotazu s funkciou COUNT(*) obsahuje klauzulu WHERE s podmienkou, funkcia vráti počet riadkov, ktoré spĺňajú zadanú podmienku. Na rozdiel od prvej formy funkcie COUNT, druhá forma neignoruje hodnoty NULL, pretože táto funkcia pracuje s riadkami, nie so stĺpcami. Nižšie uvedený príklad ukazuje použitie funkcie COUNT(*):

USE SampleDb; SELECT Job AS "Typ práce", COUNT(*) "Potrebujem pracovníkov" FROM Works_on GROUP BY Job;

Tu sa počíta počet pozícií vo všetkých projektoch. Výsledok vykonania dotazu:

Funkcia COUNT_BIG podobne ako funkcia COUNT. Jediný rozdiel medzi nimi je typ výsledku, ktorý vracajú: funkcia COUNT_BIG vždy vracia hodnoty BIGINT, zatiaľ čo funkcia COUNT vracia hodnoty údajov INTEGER.

Štatistické agregované funkcie

Nasledujúce funkcie tvoria skupinu štatistických agregovaných funkcií:

VAR

Vypočíta štatistický rozptyl všetkých hodnôt zobrazených v stĺpci alebo výraze.

VARP

Vypočíta štatistický rozptyl populácie všetkých hodnôt zobrazených v stĺpci alebo výraze.

STDEV

Vypočíta štandardnú odchýlku (ktorá sa vypočíta ako druhá odmocnina zodpovedajúceho rozptylu) všetkých hodnôt v stĺpci alebo výraze.

STDEVP

Vypočíta štandardnú odchýlku súhrnu všetkých hodnôt v stĺpci alebo výraze.

Používateľom definované agregačné funkcie

Databázový stroj tiež podporuje implementáciu užívateľom definovaných funkcií. Táto schopnosť umožňuje užívateľom rozšíriť systémové agregované funkcie o funkcie, ktoré môžu implementovať a nainštalovať sami. Tieto funkcie predstavujú špeciálnu triedu užívateľom definovaných funkcií a podrobnejšie sa o nich porozprávame neskôr.

MÁME ponuku

Vo vete MAJÚCE definuje podmienku, ktorá sa vzťahuje na skupinu riadkov. Táto klauzula má teda pre skupiny riadkov rovnaký význam ako klauzula WHERE pre obsah zodpovedajúcej tabuľky. Syntax klauzuly HAVING je nasledovná:

MAJÚ PODMIENKY

Parameter podmienky tu predstavuje podmienku a obsahuje agregované funkcie alebo konštanty.

Použitie klauzuly HAVING s agregovanou funkciou COUNT(*) je uvedené v príklade nižšie:

USE SampleDb; -- Vráti "p3" SELECT ProjectNumber FROM Works_on GROUP BY ProjectNumber HAVING COUNT(*)

V tomto príklade pomocou klauzuly GROUP BY systém zoskupí všetky riadky na základe hodnôt v stĺpci ProjectNumber. Potom sa spočíta počet riadkov v každej skupine a vyberú sa skupiny obsahujúce menej ako štyri riadky (tri alebo menej).

Klauzula HAVING sa môže použiť aj bez agregačných funkcií, ako je znázornené v príklade nižšie:

USE SampleDb; -- Vráti "Consultant" SELECT Job FROM Works_on GROUP BY Job HAVING Job LIKE "K%";

Tento príklad zoskupuje riadky v tabuľke Works_on podľa pozície a eliminuje tie pozície, ktoré nezačínajú písmenom "K".

Klauzula HAVING sa môže použiť aj bez klauzuly GROUP BY, aj keď to nie je bežná prax. V tomto prípade sú všetky riadky tabuľky vrátené v rovnakej skupine.

OBJEDNAJ PODĽA ponuky

Veta ZORADIŤ PODĽA definuje poradie zoradenia riadkov v sade výsledkov vrátených dotazom. Táto veta má nasledujúcu syntax:

Poradie triedenia je špecifikované v parametri col_name. Parameter col_number je alternatívny špecifikátor poradia zoradenia, ktorý identifikuje stĺpce v poradí, v akom sa zobrazujú vo výberovom zozname príkazu SELECT (1 je prvý stĺpec, 2 je druhý stĺpec atď.). parameter ASC definuje triedenie vo vzostupnom poradí a parameter DESC- klesajúci. Predvolená hodnota je ASC.

Názvy stĺpcov v klauzule ORDER BY nemusia byť v zozname vybratých stĺpcov. Ale to neplatí pre SELECT DISTINCT dopyty, pretože v takýchto dotazoch musia byť názvy stĺpcov uvedené v klauzule ORDER BY špecifikované aj v zozname výberových stĺpcov. Okrem toho táto klauzula nemôže obsahovať názvy stĺpcov z tabuliek, ktoré nie sú uvedené v klauzule FROM.

Ako môžete vidieť zo syntaxe klauzuly ORDER BY, množinu výsledkov možno triediť podľa viacerých stĺpcov. Toto triedenie je znázornené na príklade nižšie:

Tento príklad vyberá čísla oddelení a priezviská a krstné mená zamestnancov pre zamestnancov, ktorých osobné číslo je menšie ako 20 000, a zoraďuje ich podľa priezviska a mena. Výsledkom tohto dotazu je:

Stĺpce v klauzule ORDER BY môžu byť špecifikované nie podľa ich názvov, ale podľa poradia vo výberovom zozname. V súlade s tým môže byť veta vo vyššie uvedenom príklade prepísaná takto:

Tento alternatívny spôsob určenia stĺpcov podľa ich pozície namiesto ich názvov platí, ak kritérium zoradenia obsahuje súhrnnú funkciu. (Ďalším spôsobom je použitie názvov stĺpcov, ktoré sa potom objavia v klauzule ORDER BY.) V klauzule ORDER BY sa však odporúča špecifikovať stĺpce ich názvami a nie číslami, aby sa uľahčila aktualizácia dotazu ak je potrebné pridať alebo odstrániť stĺpce z výberového zoznamu. Určenie stĺpcov v klauzule ORDER BY ich číslami je znázornené v príklade nižšie:

USE SampleDb; SELECT ProjectNumber, COUNT(*) "Počet zamestnancov" FROM Works_on GROUP BY ProjectNumber ORDER BY 2 DESC;

Tu sa pre každý projekt vyberie číslo projektu a počet zamestnancov, ktorí sa na ňom podieľajú, pričom sa výsledok zoradí v zostupnom poradí podľa počtu zamestnancov.

Transact-SQL umiestňuje hodnoty NULL na začiatok zoznamu pri zoradení vo vzostupnom poradí a na koniec zoznamu pri zostupnom poradí.

Použitie klauzuly ORDER BY na stránkovanie výsledkov

Zobrazenie výsledkov dotazu na aktuálnej stránke môže byť implementované buď v užívateľskej aplikácii, alebo na to môže dostať pokyn databázový server. V prvom prípade sú všetky riadky databázy odoslané do aplikácie, ktorej úlohou je vybrať požadované riadky a zobraziť ich. V druhom prípade sa zo strany servera vyberú a zobrazia iba riadky požadované pre aktuálnu stránku. Ako by ste mohli očakávať, generovanie stránok na strane servera zvyčajne poskytuje lepší výkon, pretože klientovi sa posielajú len riadky potrebné na zobrazenie.

Na podporu vytvárania stránok na strane servera SQL Server 2012 zavádza dve nové klauzuly príkazu SELECT: OFFSET a FETCH. Aplikácia týchto dvoch viet je demonštrovaná v príklade nižšie. Tu sa z databázy AdventureWorks2012 (ktorú nájdete v zdrojoch) získajú obchodné ID, pracovné zaradenie a dátum narodenia všetkých zamestnankýň, pričom sa výsledok zoradí vzostupne podľa pracovnej pozície. Výsledná sada riadkov sa rozdelí na 10-riadkové strany a zobrazí sa tretia strana:

Vo vete OFFSET určuje počet riadkov výsledkov, ktoré sa majú v zobrazenom výsledku preskočiť. Toto číslo sa vypočíta po zoradení riadkov podľa klauzuly ORDER BY. Vo vete NÁSLEDUJÚCE NAČÍTANIE určuje počet zodpovedajúcich WHERE a zoradených riadkov, ktoré sa majú vrátiť. Parameter tejto klauzuly môže byť konštanta, výraz alebo výsledok iného dotazu. Klauzula FETCH NEXT je podobná klauzule NAJPRV ZÍSKAJTE.

Hlavným cieľom pri vytváraní stránok na strane servera je schopnosť implementovať bežné formuláre stránok pomocou premenných. Túto úlohu môžete vykonať prostredníctvom balíka SQL Server.

príkaz SELECT a vlastnosť IDENTITY

IDENTITA Nehnuteľnosť umožňuje definovať hodnoty pre konkrétny stĺpec tabuľky ako automaticky sa zvyšujúce počítadlo. Túto vlastnosť môžu mať stĺpce s číselnými typmi údajov, ako sú TINYINT, SMALLINT, INT a BIGINT. Pre takýto stĺpec tabuľky databázový stroj automaticky generuje sekvenčné hodnoty začínajúce od zadanej počiatočnej hodnoty. Vlastnosť IDENTITY teda možno použiť na generovanie jednociferných číselných hodnôt pre vybraný stĺpec.

Tabuľka môže obsahovať iba jeden stĺpec s vlastnosťou IDENTITY. Vlastník tabuľky má možnosť zadať počiatočnú hodnotu a prírastok, ako je uvedené v príklade nižšie:

USE SampleDb; VYTVORIŤ TABUĽKU Produkt (Id INT IDENTITY(10000, 1) NOT NULL, Name NVARCHAR(30) NOT NULL, Price MONEY) INSERT INTO Product(Name, Price) VALUES ("Item1", 10), ("Item2", 15) , ("Položka 3", 8), ("Položka 4", 15), ("Položka 5", 40); -- Vráti 10004 SELECT IDENTITYCOL FROM Product WHERE Name = "Product5"; -- Podobne ako v predchádzajúcom vyhlásení SELECT $identity FROM Product WHERE Name = "Product5";

Tento príklad najprv vytvorí tabuľku produktu, ktorá obsahuje stĺpec Id s vlastnosťou IDENTITY. Hodnoty v stĺpci Id sú generované automaticky systémom, začínajúc od 10 000 a zvyšujú sa o jednu pre každú nasledujúcu hodnotu: 10 000, 10 001, 10 002 atď.

S vlastnosťou IDENTITY je spojených niekoľko systémových funkcií a premenných. Napríklad používa príklad kódu $systémová premenná identity. Ako môžete vidieť z výstupu tohto kódu, táto premenná automaticky odkazuje na vlastnosť IDENTITY. Namiesto toho môžete použiť aj systémovú funkciu IDENTITYCOL.

Počiatočnú hodnotu a prírastok stĺpca s vlastnosťou IDENTITY možno nájsť pomocou funkcií IDENT_SEED a IDENT_INCR resp. Tieto funkcie sa uplatňujú takto:

USE SampleDb; SELECT IDENT_SEED("Produkt"), IDENT_INCR("Produkt")

Ako už bolo spomenuté, hodnoty IDENTITY nastavuje systém automaticky. Používateľ však môže explicitne určiť svoje vlastné hodnoty pre určité riadky nastavením parametra IDENTITY_INSERT ON pred vložením explicitnej hodnoty:

SET IDENTITY INSERT názov tabuľky ON

Pretože možnosť IDENTITY_INSERT možno použiť na nastavenie ľubovoľnej hodnoty pre stĺpec vlastnosti IDENTITY vrátane duplicitnej hodnoty, vlastnosť IDENTITY zvyčajne nevynucuje jedinečnosť hodnôt stĺpca. Preto by sa na vynútenie jedinečnosti hodnôt stĺpcov mali použiť obmedzenia UNIQUE alebo PRIMARY KEY.

Pri vkladaní hodnôt do tabuľky potom, čo je IDENTITY_INSERT zapnuté, systém vytvorí ďalšiu hodnotu stĺpca IDENTITY zvýšením najväčšej aktuálnej hodnoty tohto stĺpca.

príkaz CREATE SEQUENCE

Používanie vlastnosti IDENTITY má niekoľko významných nevýhod, z ktorých najvýznamnejšie sú tieto:

aplikácia vlastnosti je obmedzená na špecifikovanú tabuľku;

novú hodnotu stĺpca nie je možné získať iným spôsobom ako jej aplikáciou;

vlastnosť IDENTITY je možné zadať iba pri vytváraní stĺpca.

Z týchto dôvodov SQL Server 2012 zavádza sekvencie, ktoré majú rovnakú sémantiku ako vlastnosť IDENTITY, ale bez vyššie uvedených nevýhod. V tomto kontexte je sekvencia databázová funkcia, ktorá vám umožňuje špecifikovať hodnoty počítadiel pre rôzne databázové objekty, ako sú stĺpce a premenné.

Sekvencie sa vytvárajú pomocou inštrukcie VYTVORIŤ SEKVENCIU. Príkaz CREATE SEQUENCE je definovaný v štandarde SQL a je podporovaný inými relačnými databázovými systémami, ako sú IBM DB2 a Oracle.

Nižšie uvedený príklad ukazuje, ako vytvoriť sekvenciu na serveri SQL Server:

USE SampleDb; CREATE SEQUENCE dbo.Sequence1 AS INT ZAČNITE S 1 PRÍRODKOM O 5 MINHODNOTA 1 MAXIMÁLNA HODNOTA 256 CYKLUS;

Vo vyššie uvedenom príklade sú hodnoty Sequence1 generované automaticky systémom, počnúc hodnotou 1 s prírastkami po 5 pre každú nasledujúcu hodnotu. Teda v ponuka START je špecifikovaná počiatočná hodnota a Ponuka INCREMENT- krok. (Krok môže byť pozitívny alebo negatívny.)

V nasledujúcich dvoch nepovinných vetách MINVALUE a MAX sú špecifikované minimálne a maximálne hodnoty sekvenčného objektu. (Všimnite si, že hodnota MINVALUE musí byť menšia alebo rovná počiatočnej hodnote a hodnota MAXVALUE nemôže byť väčšia ako horný limit typu údajov špecifikovaného pre sekvenciu.) V klauzule CYKLUS označuje, že sekvencia sa opakuje od začiatku, keď je prekročená maximálna (alebo minimum pre sekvenciu so záporným krokom). Štandardne je táto klauzula NO CYCLE, čo znamená, že prekročenie maximálnej alebo minimálnej hodnoty sekvencie spôsobí výnimku.

Hlavnou črtou sekvencií je ich nezávislosť od tabuliek, t.j. môžu byť použité s akýmkoľvek databázovým objektom, ako sú stĺpce tabuľky alebo premenné. (Táto vlastnosť má pozitívny vplyv na úložisko a tým aj na výkon. Špecifickú sekvenciu nie je potrebné ukladať, ukladá sa len jej posledná hodnota.)

Nové sekvenčné hodnoty sa vytvárajú pomocou ĎALŠIA HODNOTA PRE výrazy, ktorého použitie je uvedené v príklade nižšie:

USE SampleDb; -- Vráti 1 SELECT NEXT VALUE FOR dbo.sequence1; -- Vráti 6 (ďalší krok) SELECT NEXT VALUE FOR dbo.sequence1;

Na priradenie výsledku sekvencie k bunke premennej alebo stĺpca môžete použiť výraz NEXT VALUE FOR. Nižšie uvedený príklad ukazuje použitie tohto výrazu na priradenie výsledkov k stĺpcu:

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

Vyššie uvedený príklad najskôr vytvorí tabuľku produktu so štyrmi stĺpcami. Ďalej dva príkazy INSERT vložia do tejto tabuľky dva riadky. Prvé dve bunky v prvom stĺpci budú mať hodnoty 11 a 16.

Nižšie uvedený príklad ukazuje použitie zobrazenia katalógu sys.sekvencie ak chcete zobraziť aktuálnu hodnotu sekvencie bez jej použitia:

Typicky sa výraz NEXT VALUE FOR používa v príkaze INSERT, aby prinútil systém vložiť vygenerované hodnoty. Tento výraz možno použiť aj ako súčasť viacriadkového dotazu pomocou klauzuly OVER.

Ak chcete zmeniť vlastnosť existujúcej sekvencie, použite príkaz ALTER SEQUENCE. Jedným z najdôležitejších použití tohto príkazu je možnosť RESTART WITH, ktorá resetuje zadanú sekvenciu. Nasledujúci príklad ukazuje použitie príkazu ALTER SEQUENCE na resetovanie takmer všetkých vlastností Sequence1:

USE SampleDb; ALTER SEQUENCE dbo.sequence1 REŠTARTOVAŤ SO 100 PRÍRODKOM O 50 MINHODNOTA 50 MAX.HODNOTA 200 ŽIADNY CYKLUS;

Vymažte sekvenciu pomocou inštrukcie DROP SEQUENCE.

Nastaviť operátorov

Okrem operátorov diskutovaných vyššie podporuje Transact-SQL ďalšie tri množinové operátory: UNION, INTERSECT a EXCEPT.

operátor UNION

operátor UNION kombinuje výsledky dvoch alebo viacerých dotazov do jednej sady výsledkov, ktorá obsahuje všetky riadky, ktoré patria všetkým dotazom v spojení. Výsledkom spojenia dvoch tabuliek je teda nová tabuľka obsahujúca všetky riadky zahrnuté v jednej z pôvodných tabuliek alebo oboch týchto tabuliek.

Všeobecná forma operátora UNION vyzerá takto:

select_1 UNION select_2(select_3])...

Možnosti select_1, select_2, ... sú príkazy SELECT, ktoré vytvárajú spojenie. Ak sa použije možnosť VŠETKY, zobrazia sa všetky riadky vrátane duplikátov. V operátori UNION má parameter ALL rovnaký význam ako vo výberovom zozname SELECT, s jedným rozdielom: pre výberový zoznam SELECT sa tento parameter používa štandardne, ale pre operátor UNION musí byť špecifikovaný explicitne.

Databáza SampleDb v pôvodnej podobe nie je vhodná na demonštráciu použitia operátora UNION. Preto táto sekcia vytvorí novú tabuľku EmployeeEnh, ktorá je identická s existujúcou tabuľkou Zamestnanec, ale má ďalší stĺpec Mesto. Tento stĺpec označuje, kde zamestnanci bývajú.

Vytvorenie tabuľky EmployeeEnh nám poskytuje príležitosť preukázať použitie klauzuly DO v príkaze SELECT. Príkaz SELECT INTO vykoná dve operácie. Najprv sa vytvorí nová tabuľka so stĺpcami uvedenými v zozname SELECT. Potom sa riadky pôvodnej tabuľky vložia do novej tabuľky. Názov novej tabuľky je špecifikovaný v klauzule INTO a názov zdrojovej tabuľky je špecifikovaný v klauzule FROM.

Príklad nižšie ukazuje vytvorenie tabuľky EmployeeEnh z tabuľky Zamestnanec:

USE SampleDb; SELECT * INTO EmployeeEnh FROM Zamestnanec; ALTER TABLE EmployeeEnh ADD City NCHAR(40) NULL;

V tomto príklade príkaz SELECT INTO vytvorí tabuľku EmployeeEnh, vloží do nej všetky riadky zo zdrojovej tabuľky Zamestnanec a potom príkaz ALTER TABLE pridá stĺpec Mesto do novej tabuľky. Pridaný stĺpec Mesto však neobsahuje žiadne hodnoty. Hodnoty v tomto stĺpci je možné vložiť cez Management Studio alebo pomocou nasledujúceho kódu:

USE SampleDb; UPDATE EmployeeEnh SET City="Kazan" WHERE Id=2581; UPDATE EmployeeEnh SET Mesto = "Moskva" WHERE Id = 9031; UPDATE EmployeeEnh SET Mesto = "Jekaterinburg" WHERE Id = 10102; UPDATE EmployeeEnh SET Mesto = "Saint Petersburg" WHERE Id = 18316; UPDATE EmployeeEnh SET Mesto = "Krasnodar" WHERE Id = 25348; UPDATE EmployeeEnh SET City="Kazan" WHERE Id=28559; UPDATE EmployeeEnh SET City="Perm" WHERE Id=29346;

Teraz sme pripravení demonštrovať použitie príkazu UNION. Nižšie uvedený príklad ukazuje dotaz na vytvorenie spojenia medzi tabuľkami EmployeeEnh a Department pomocou tohto príkazu:

USE SampleDb; SELECT City AS "Mesto" FROM EmployeeEnh UNION SELECT Location FROM Department;

Výsledkom tohto dotazu je:

Pomocou príkazu UNION možno spájať iba kompatibilné tabuľky. Kompatibilnými tabuľkami rozumieme, že oba zoznamy stĺpcov vo výbere musia obsahovať rovnaký počet stĺpcov a že zodpovedajúce stĺpce musia mať kompatibilné dátové typy. (Pokiaľ ide o kompatibilitu, typy údajov INT a SMALLINT nie sú kompatibilné.)

Výsledok spojenia je možné zoradiť iba pomocou klauzuly ORDER BY v poslednom príkaze SELECT, ako je uvedené v príklade nižšie. Klauzuly GROUP BY a HAVING možno použiť s jednotlivými príkazmi SELECT, ale nie v rámci samotného spojenia.

Dotaz v tomto príklade načíta zamestnancov, ktorí buď pracujú v oddelení d1, alebo začali pracovať na projekte pred 1. januárom 2008.

Operátor UNION podporuje možnosť ALL. Keď sa použije táto možnosť, duplikáty sa zo sady výsledkov neodstránia. Operátor OR môžete použiť namiesto operátora UNION, ak všetky príkazy SELECT spojené jedným alebo viacerými operátormi UNION odkazujú na rovnakú tabuľku. V tomto prípade je množina príkazov SELECT nahradená jedným príkazom SELECT so množinou príkazov OR.

Príkazy INTERSECT a EXCEPT

Dvaja ďalší operátori na prácu so súpravami, INTERSECT a OKREM, definovať priesečník a rozdiel, resp. Pod priesečníkom je v tomto kontexte množina riadkov, ktoré patria do oboch tabuliek. A rozdiel dvoch tabuliek je definovaný ako všetky hodnoty, ktoré patria do prvej tabuľky a nie sú prítomné v druhej. Nasledujúci príklad ukazuje použitie príkazu INTERSECT:

Transact-SQL nepodporuje použitie voľby ALL s príkazom INTERSECT ani s príkazom EXCEPT. Použitie príkazu EXCEPT je znázornené v príklade nižšie:

Majte na pamäti, že tieto tri množinové operátory majú odlišnú prioritu vykonávania: operátor INTERSECT má najvyššiu prioritu, za ním nasleduje operátor EXCEPT a operátor UNION má najnižšiu prioritu. Nepozornosť na prioritu vykonávania pri použití viacerých operátorov rôznych množín môže viesť k neočakávaným výsledkom.

CASE výrazy

V oblasti programovania databázových aplikácií je niekedy potrebné upraviť prezentáciu údajov. Napríklad ľudí možno rozdeliť kódovaním podľa ich sociálnej príslušnosti pomocou hodnôt 1, 2 a 3, ktoré označujú mužov, ženy a deti. Táto programovacia technika môže skrátiť čas potrebný na implementáciu programu. výraz CASE Jazyk Transact-SQL uľahčuje implementáciu tohto typu kódovania.

Na rozdiel od väčšiny programovacích jazykov CASE nie je výrok, ale výraz. Preto výraz CASE možno použiť takmer všade, kde jazyk Transact-SQL umožňuje použitie výrazov. Výraz CASE má dve formy:

jednoduchý výraz CASE;

hľadaný výraz CASE.

Syntax jednoduchého výrazu CASE je nasledovná:

Príkaz s jednoduchým výrazom CASE najprv prehľadá zoznam všetkých výrazov v klauzula WHEN prvý výraz, ktorý sa zhoduje s výrazom_1 a potom vykoná zodpovedajúci POTOM doložka. Ak v zozname WHEN nie je žiadny zodpovedajúci výraz, potom klauzula ELSE.

Syntax pre vyhľadávací výraz CASE je:

V tomto prípade sa vyhľadá prvá podmienka zhody a potom sa vykoná zodpovedajúca klauzula THEN. Ak žiadna z podmienok nevyhovuje požiadavkám, vykoná sa klauzula ELSE. Použitie vyhľadávacieho výrazu CASE je znázornené v príklade nižšie:

USE SampleDb; SELECT ProjectName, CASE WHEN Budget > 0 AND Budget 100000 AND Budget 150000 AND Budget

Výsledkom tohto dotazu je:

Tento príklad váži rozpočty všetkých projektov a zobrazuje ich vypočítané váhy spolu s príslušnými názvami projektov.

Nižšie uvedený príklad ukazuje ďalší spôsob použitia výrazu CASE, kde klauzula WHEN obsahuje poddotazy, ktoré sú súčasťou výrazu:

USE SampleDb; SELECT ProjectName, CASE WHEN p1.Budget (SELECT AVG(p2.Budget) FROM Project p2) THEN "Nadpriemerné" END "Kategória rozpočtu" FROM Project p1;

Výsledok tohto dotazu je nasledujúci:

Norma ISO definuje nasledujúcich päť agregačné funkcie:

COUNT– vráti počet hodnôt v zadanom stĺpci;

SUM– vráti súčet hodnôt v zadanom stĺpci;

AVG– vráti priemernú hodnotu v zadanom stĺpci;

MIN– vráti minimálnu hodnotu v zadanom stĺpci;

MAX- vráti maximálnu hodnotu v zadanom stĺpci.

Všetky tieto funkcie pracujú s hodnotami v jednom stĺpci tabuľky a vracajú jednu hodnotu. Funkcie COUNT, MIN a MAX sa vzťahujú na numerické aj nenumerické polia, zatiaľ čo funkcie SUM a AVG možno použiť len pre numerické polia. S výnimkou COUNT(*) sa pri výpočte výsledkov akýchkoľvek funkcií najskôr eliminujú všetky nulové hodnoty, po ktorých sa požadovaná operácia aplikuje len na zostávajúce nenulové hodnoty stĺpca. Variant COUNT (*) je špeciálny prípad použitia funkcie COUNT – jej účelom je spočítať všetky riadky v tabuľke, či už obsahujú hodnoty null, duplikáty alebo akúkoľvek inú hodnotu. Ak chcete pred použitím agregačnej funkcie odstrániť duplicitné hodnoty, musíte pred názov stĺpca v definícii funkcie zadať kľúčové slovo DISTINCT. Norma ISO umožňuje použitie kľúčového slova ALL na explicitné označenie toho, že odstránenie duplicitných hodnôt nie je potrebné, hoci toto kľúčové slovo je predvolene zahrnuté, ak nie sú špecifikované žiadne iné kvalifikátory. Kľúčové slovo DISTINCT nemá pre funkcie MIN a MAX žiadny význam. Jeho použitie však môže ovplyvniť výsledky funkcií SUM a AVG, preto by ste mali vopred zvážiť, či by mal byť prítomný v každom konkrétnom prípade. Kľúčové slovo DISTINCT možno zadať maximálne raz v každom dotaze.

Všimnite si, že agregačné funkcie možno použiť iba v zozname výberu SELECT a v klauzule HAVING (pozri časť 5.3.4). Vo všetkých ostatných prípadoch nie je používanie týchto funkcií povolené. Ak zoznam SELECT obsahuje agregačnú funkciu a v tele dotazu nie je žiadna klauzula GROUP BY, ktorá poskytuje zoskupenie údajov, potom žiadny z prvkov výberového zoznamu SELECT nemôže obsahovať žiadne odkazy na stĺpce, pokiaľ sa tento stĺpec nepoužíva ako agregačná funkcia. parameter. Napríklad nasledujúci dopyt je neplatný:

VYBRAŤpersonálNie,COUNT (plat)

ODpersonál;

Chyba je v tom, že v tomto dotaze nie je žiadna konštrukcia GROUP BY a stĺpec staffNo v zozname SELECT je prístupný bez použitia agregačnej funkcie.

Príklad 13: Použitie funkcie COUNT(*).Zistite, koľko nehnuteľností na prenájom má nájomnú cenu vyššiu ako 350 £ mesačne,

VYBERTE POČET(*) Počet AS

ODmajetok prenajatý

KDEnájomné > 350;

Obmedzenie počítať len tie prenajaté objekty, ktorých nájomné je viac ako 350 GBP za mesiac, sa implementuje pomocou klauzuly WHERE. Celkový počet prenajatých objektov, ktoré spĺňajú zadanú podmienku, je možné určiť pomocou agregačnej funkcie COUNT. Výsledky vykonania dotazu sú uvedené v tabuľke. 23.

Tabuľka 23

počítať

Príklad 14. Pomocou funkcie COUNT(DISTINCT).Zistite, koľko rôznych nehnuteľností na prenájom si klienti skontrolovali v máji 2001.

VYBRAŤ POČET(ODLIŠNÉpropertyNo) AS počítať

ODPrezeranie

Opäť platí, že obmedzenie výsledkov dotazu na analýzu len tých prenájmov, ktoré boli skontrolované v máji 2001, sa dosiahne použitím klauzuly WHERE. Celkový počet kontrolovaných objektov, ktoré spĺňajú zadanú podmienku, je možné určiť pomocou agregačnej funkcie COUNT. Keďže však rovnaký objekt môžu rôzni klienti zobraziť viackrát, kľúčové slovo DISTINCT musí byť špecifikované v definícii funkcie, aby sa z výpočtu vylúčili duplicitné hodnoty. Výsledky vykonania dotazu sú uvedené v tabuľke. 24.

Tabuľka 24

Príklad 16. Použitie funkcií MIN, MAXnAVG.Vypočítajte hodnotu minimálnej, maximálnej a priemernej mzdy.

VYBERTE MIN(plat) AS min, MAX(plat) AS max, AVG(plat) AS priem

ODpersonál;

V tomto príklade potrebujete spracovať informácie o všetkých pracovníkoch v spoločnosti, takže nemusíte použiť klauzulu WHERE. Požadované hodnoty je možné vypočítať pomocou funkcií MIN, MAX a AVG aplikovaných na stĺpec platov v tabuľke Zamestnanci. Výsledky vykonania dotazu sú uvedené v tabuľke. 26.

Tabuľka 26

Výsledok dotazu

min	max	priem
9000.00	30000.00	17000.00

Výsledky zoskupovania (konštrukt GROUP BY). Príklady súhrnných údajov vyššie sú podobné ako súhrnné riadky, ktoré sa zvyčajne nachádzajú na konci prehľadov. Výsledkom je, že všetky podrobné údaje prehľadu sú skomprimované do jedného súhrnného riadku. Veľmi často sa však v prehľadoch vyžaduje tvoriť aj medzisúčty. Na tento účel môže byť v príkaze SELECT špecifikovaná klauzula GROUP BY. Zavolá sa dotaz, ktorý obsahuje klauzulu GROUP BY skupinový dotaz, pretože zoskupuje údaje vyplývajúce z operácie SELECT, po ktorej sa vytvorí jeden súhrnný riadok pre každú jednotlivú skupinu. Volajú sa stĺpce uvedené v klauzule GROUP BY zoskupené stĺpce. Norma ISO vyžaduje, aby klauzuly SELECT a GROUP BY spolu úzko súviseli. Pri použití klauzuly GROUP BY v príkaze SELECT musí mať každá položka zoznamu vo výberovom zozname SELECT jediná hodnota pre celú skupinu. Konštrukt SELECT môže navyše obsahovať iba nasledujúce typy prvkov:

Názvy stĺpcov;

agregačné funkcie;

konštanty;

Výrazy, ktoré zahŕňajú kombinácie vyššie uvedených prvkov.

Všetky názvy stĺpcov v zozname SELECT sa musia objaviť aj v klauzule GROUP BY, pokiaľ sa názov stĺpca nepoužíva iba v agregačnej funkcii. Opačné tvrdenie nie je vždy pravdivé – klauzula GROUP BY môže obsahovať názvy stĺpcov, ktoré nie sú v zozname SELECT. Ak sa klauzula WHERE použije spolu s klauzulou GROUP BY, potom sa spracuje ako prvá a zoskupia sa len tie riadky, ktoré spĺňajú podmienku vyhľadávania. Norma ISO špecifikuje, že keď sa vykonáva zoskupovanie, všetky chýbajúce hodnoty sa považujú za rovnaké. Ak dva riadky tabuľky v rovnakom stĺpci zoskupenia obsahujú hodnoty NULL a rovnaké hodnoty vo všetkých ostatných neprázdnych stĺpcoch zoskupenia, umiestnia sa do rovnakej skupiny.

Príklad 17: Použitie klauzuly GROUP BY.Určte počet zamestnancov pracujúcich v každom z oddelení spoločnosti, ako aj ich celkové mzdy.

VYBRAŤpobočkyNie, COUNT(počet zamestnancov) AS počítať, SUM(plat) AS súčet

ODpersonál

GROUP BYpobočka č

ZORADIŤ PODĽAčíslo pobočky;

Názvy stĺpcov staffNo a plat nie je potrebné uvádzať v zozname prvkov GROUP BY, pretože sa zobrazujú iba v zozname SELECT s agregačnými funkciami. Stĺpec branchNo v zozname klauzuly SELECT zároveň nie je spojený so žiadnou agregačnou funkciou az tohto dôvodu musí byť špecifikovaný v klauzule GROUP BY. Výsledky vykonania dotazu sú uvedené v tabuľke. 27.

Tabuľka 27

Výsledok dotazu

pobočka č	počítať	súčet
B003		54000.00
B005		39000.00
B007		9000.00

Koncepčne sa pri spracovaní tejto požiadavky vykonajú nasledujúce akcie.

1. Riadky tabuľky Zamestnanci sú rozdelené do skupín podľa hodnôt v stĺpci čísla pobočky spoločnosti. V rámci každej zo skupín sú údaje o všetkých zamestnancoch jedného z oddelení spoločnosti. V našom príklade sa vytvoria tri skupiny, ako je znázornené na obr. jeden.

2. Pre každú zo skupín sa vypočíta celkový počet riadkov, ktorý sa rovná počtu zamestnancov oddelenia, ako aj súčet hodnôt v stĺpci mzdy, čo je súčet miezd všetkých oddelení zamestnancov, o ktorých máme záujem. Potom sa vygeneruje jeden súhrnný riadok pre celú skupinu zdrojových riadkov.

3. Prijaté riadky výslednej tabuľky sú zoradené vzostupne podľa čísla pobočky uvedeného v stĺpci branchNo.

pobočka č	personál č	Plat
B003	SG37	12000.00
B003	SG14	18000.00
B003	SG5	24000.00
B005	SL21	30000.00
B005	SL41	9000.00
B007	SA9	9000.00

COUNT(počet zamestnancov)	SUM(plat)
	54000.00
	39000.00
	9000.00

Ryža. 1. Tri skupiny záznamov vytvorené pri vykonávaní dotazu

Štandard SQL umožňuje umiestniť poddotazy do výberového zoznamu SELECT. Preto vyššie uvedený dotaz môže byť reprezentovaný aj takto:

VYBRAŤčíslo pobočky, (VYBRAŤ POČET(Číslo personálu)AS počítať

ODzamestnanci s

KDEs.branchNo = b.branchNo),

(SELECT SUM(plat) AS sum

ODzamestnanci s

KDEs.branchNo = b.branchNo)

ODpobočka b

ZORADIŤ PODĽAčíslo pobočky;

V tejto verzii dotazu sa však pre každú z pobočiek spoločnosti opísaných v tabuľke Pobočiek vygenerujú dva výsledky výpočtu agregačných funkcií, takže v niektorých prípadoch sa môžu objaviť riadky obsahujúce nulové hodnoty.

Obmedzenia zoskupovania (konštrukt HAVING). Klauzula HAVING je určená na použitie v spojení s klauzulou GROUP BY na špecifikovanie obmedzení na výber tých skupiny, ktorý bude umiestnený vo výslednej tabuľke dotazov. Hoci klauzuly HAVING a WHERE majú podobnú syntax, ich účel je odlišný. Klauzula WHERE je určená na výber jednotlivých riadkov určených na vyplnenie výslednej tabuľky dotazov a konštrukcia HAVING sa používa na výber skupiny, umiestnené vo výslednej tabuľke dotazov. Norma ISO vyžaduje, aby názvy stĺpcov použité v klauzule HAVING boli prítomné v zozname prvkov GROUP BY alebo použité v agregačných funkciách. V praxi podmienky vyhľadávania v klauzule HAVING vždy obsahujú aspoň jednu agregačnú funkciu; v opačnom prípade by sa tieto hľadané výrazy mali umiestniť do klauzuly WHERE a použiť na výber jednotlivých riadkov. (Pamätajte, že agregačné funkcie nemožno použiť v klauzule WHERE.) Klauzula HAVING nie je nevyhnutnou súčasťou jazyka SQL – každý dotaz napísaný pomocou klauzuly HAVING môže byť reprezentovaný inak bez jej použitia.

Príklad 18: Použitie konštruktu HAVING.Pre každú pobočku spoločnosti s viac ako jedným zamestnancom určite počet zamestnancov a výšku ich miezd.

VYBRAŤpobočkyNie, COUN T (č. personálu) AS počítať, SUM(plat) AS súčet

ODpersonál

GROUP BYpobočka č

S POČÍTANÍM(počet zamestnancov) > 1

ZORADIŤ PODĽAčíslo pobočky;

Tento príklad je podobný predchádzajúcemu, ale používa dodatočné obmedzenia na označenie, že nás zaujímajú iba informácie o tých oddeleniach spoločnosti, ktoré zamestnávajú viac ako jednu osobu. Podobná požiadavka platí pre skupiny, takže dotaz by mal používať konštrukciu HAVING. Výsledky vykonania dotazu sú uvedené v tabuľke. 28.

Tabuľka 28

pobočka Bez súčtu

В00З 3 54000,00

B005 2 39000,00

Poddotazy. V tejto časti budeme diskutovať o použití úplných príkazov SELECT vložených do tela iného príkazu SELECT. Vonkajšie(Druhý) príkaz SELECT používa výsledok vykonania interné(prvý) výrok na určenie obsahu konečného výsledku celej operácie. Vnútorné dotazy môžu byť v klauzulách WHERE a HAVING vonkajšieho príkazu SELECT, v takom prípade sa nazývajú poddotazy alebo vnorené dopyty. Interné príkazy SELECT možno použiť aj v príkazoch INSERT, UPDATE a DELETE . Existujú tri typy poddotazov.

Skalárny poddotaz vráti hodnotu vybranú z priesečníka jedného stĺpca s jedným riadkom, t.j. jediná hodnota. V zásade je možné skalárny poddotaz použiť všade tam, kde je potrebná jedna hodnota. Varianty skalárnych poddotazov sú uvedené v príkladoch 13 a 14.

Poddotaz reťazca vráti hodnoty viacerých stĺpcov tabuľky, ale ako jeden riadok. Reťazový poddotaz možno použiť kdekoľvek, kde sa používa konštruktor reťazcovej hodnoty – zvyčajne predikáty. Variant poddotazu reťazca je zobrazený v príklade 15.

Poddotaz tabuľky vráti hodnoty jedného alebo viacerých stĺpcov tabuľky s viac ako jedným riadkom. Poddotaz tabuľky možno použiť všade tam, kde je povolená tabuľka, ako je napríklad operand predikátu IN.

Príklad 19: Použitie poddotazu s testom rovnosti. Makeup zoznam zamestnancov pracujúcich v pobočke spoločnosti, ktorá sa nachádza na adrese 463 Main St1.

VYBRAŤ

ODpersonál

KDEčíslo pobočky = (VYBERTE číslo pobočky

ODpobočka

KDEulica = "163 Main S t ");

Interný príkaz SELECT (SELECT branchNo FROM Branch ...) je určený na určenie čísla pobočky spoločnosti so sídlom na "163 Main St". (Existuje iba jedna takáto pobočka spoločnosti, takže tento príklad je príkladom skalárneho poddotazu.) Po získaní požadovaného čísla pobočky sa vykoná externý poddotaz na získanie podrobných informácií o zamestnancoch v danej pobočke. Inými slovami, vnútorný príkaz SELECT vráti tabuľku pozostávajúcu z jedinej hodnoty „BOOV". Ide o číslo pobočky spoločnosti umiestnenej na adrese „163 Main St1. Výsledkom je, že vonkajší príkaz SELECT bude:

VYBRAŤstaffNo, fName, Iname, position

ODpersonál

KDEčíslo pobočky = "B0031;

Výsledky tohto dotazu sú uvedené v tabuľke. 29.

Tabuľka 29

Výsledok dotazu

personál č	fName	Volám sa	pozíciu
SG37	Ann	Buk	asistent
SG14	David	Ford	Dozorca
SG5	Susan	značka	manažér

Poddotaz je nástroj na vytvorenie dočasnej tabuľky, ktorej obsah sa načíta a spracuje externým príkazom. Poddotaz možno zadať priamo za operátormi porovnávania (t. j. operátory =,<, >, <=, >=, <>) v klauzule WHERE alebo HAVING. Text poddotazu musí byť uzavretý v zátvorkách.

Príklad 20. Používanie poddotazov s agregačnými funkciami. Urobte si zoznam všetkých zamestnancov, ktorí sú platení nadpriemerne, a uveďte, o koľko ich mzda prevyšuje priemernú mzdu v podniku.

VYBRAŤstaffNo, fName, INname, pozícia, plat - ( VYBERTE AVG(plat) OD personál) AS salDiff

ODpersonál

KDEplat > ( VYBERTE AVG(plat) OD St a f f);

Treba poznamenať, že človek nemôže priamo zahrnúť do výraz dotazu"WHERE plat > AVG(plat)", od používania agregácie funkcie v klauzule WHERE nie sú povolené. Ak chcete dosiahnuť požadovaný výsledok, mali by ste vytvoriť poddotaz, ktorý vypočíta priemernú ročnú mzdu a potom ho použiť vo vonkajšom príkaze SELECT na výber informácií o tých zamestnancoch v spoločnosti, ktorých mzda presahuje tento priemer. Inými slovami, poddotaz vráti priemernú mzdu spoločnosti za rok, čo je 17 000 libier.

Výsledok vykonania tohto skalárneho poddotazu sa používa vo vonkajšom príkaze SELECT ako na výpočet odchýlky miezd od priemernej úrovne, tak aj na výber informácií o zamestnancoch. Takže vonkajší príkaz SELECT sa stáva:

VYBRAŤstaffNo, fName, INname, pozícia, plat - 17000 Ako salDiff

ODpersonál

KDEmzda > 17000;

Výsledky vykonania dotazu sú uvedené v tabuľke. tridsať.

Tabuľka 30

Výsledok dotazu

personál č	fName	Volám sa	pozíciu	salDiff
SL21	John	biely	manažér	13000.00
SG14	David	Ford	Dozorca	1000.00
SG5	Susan	značka	manažér	7000.00

Uplatňujú sa poddotazy dodržiavanie pravidiel a obmedzenia.

1. Poddotazy by nemali používať klauzulu ORDER BY, hoci môže byť prítomná vo vonkajšom príkaze SELECT.

2. Zoznam SELECT poddotazu musí pozostávať z názvov jednotlivých stĺpcov alebo výrazov z nich vytvorených, pokiaľ v poddotaze nie je použité kľúčové slovo EXISTS.

3. Názvy stĺpcov v poddotaze sa štandardne vzťahujú na tabuľku, ktorej názov je uvedený v klauzule FROM poddotazu. Je však tiež povolené odkazovať na stĺpce tabuľky špecifikované v klauzule FROM vonkajšieho dotazu pomocou kvalifikovaných názvov stĺpcov (ako je popísané nižšie).

4. Ak je poddotaz jedným z dvoch operandov zahrnutých do operácie porovnania, potom musí byť poddotaz špecifikovaný na pravej strane tejto operácie. Napríklad zápis dotazu z predchádzajúceho príkladu nižšie je nesprávny, pretože poddotaz je umiestnený na ľavej strane operácie porovnania oproti hodnote stĺpca mzdy.

VYBRAŤ

ODpersonál

KDE(VYBERTE AVG (plat) ZO zamestnancov)< salary;

Príklad 21. Vnorené poddotazy a použitie predikátu IN. Vytvorte zoznam nehnuteľností na prenájom, pre ktoré zamestnanci pobočky spoločnosti so sídlom na adrese „163 Main st1.

VYBRAŤpropertyNo, ulica, mesto, PSČ, typ, izby, nájomné

ODmajetok prenajatý

Kapitola 5. Jazyk SQL: Manipulácia s údajmi 189

KDEstaffNo IN (SELECT staffNo

ODpersonál

KDEbrancliNo = (VYBERTE číslo pobočky

ODpobočka

KDEulica = "163 Main St"));

Prvý, najinternejší dotaz je určený na určenie čísla pobočky spoločnosti na adrese 463 Main St. údajov, a preto nemôžete použiť porovnávací operátor =.Namiesto toho musíte použiť kľúčové slovo IN.Externý dotaz získa informácie o prenajatých objektoch, za ktoré sú zodpovední zamestnanci spoločnosti, údaje o ktorých boli získané ako výsledok medzidotazu.Výsledky dopytu sú uvedené v tabuľke 31.

Tabuľka 31

Výsledok dotazu

č	ulica	mesto	PSČ	typu	izby	prenajať
PG16	5 Novar Dr	Glasgow	G129AX	Plochý
PG36	2 Manor Road	Glasgow	G324QX	Plochý
PG21	18 Dale Road	Glasgow	G12	dom

Kľúčové slová ANY a ALL. Kľúčové slová ANY a ALL možno použiť s poddotazmi, ktoré vracajú jeden stĺpec čísel. Ak pred poddotazom predchádza kľúčové slovo ALL, podmienka porovnania sa považuje za splnenú, iba ak platí pre všetky hodnoty v stĺpci výsledkov poddotazu. Ak pred textom poddotazu predchádza kľúčové slovo ANY, potom sa podmienka porovnania bude považovať za splnenú, ak bude splnená aspoň pre jednu (jednu alebo viac) hodnôt vo výslednom stĺpci poddotazu. Ak je výsledkom poddotazu prázdna hodnota, potom pre kľúčové slovo ALL sa podmienka porovnania bude považovať za splnenú a pre kľúčové slovo ANY bude považovaná za neúspešnú. Podľa normy ISO môžete navyše použiť kľúčové slovo SOME, ktoré je synonymom pre kľúčové slovo ANY.

Príklad 22. Pomocou kľúčových slov ANY a SOME. Nájdite všetkých zamestnancov, ktorých plat prevyšuje aspoň plat jeden zamestnanec pobočky spoločnosti pod číslom „booz“.

VYBRAŤstaffNo, fName, INname, pozícia, plat

ODpersonál

KDEplat > NIEKTORÉ (VYBERTE plat

ODpersonál

KDEbranchNo="B003");

Hoci tento dotaz možno napísať pomocou poddotazu, ktorý špecifikuje minimálnu mzdu pre zamestnancov oddelenia s číslom „WHO“, po ktorom môže vonkajší poddotaz vybrať informácie o všetkých zamestnancoch spoločnosti, ktorých plat presahuje túto hodnotu (pozri príklad 20), je možný aj iný prístup spočívajúci v použití kľúčových slov SOME/ANY. V tomto prípade vnútorný poddotaz vygeneruje množinu hodnôt (12 000, 18 000, 24 000) a vonkajší dotaz vyberie podrobnosti o tých pracovníkoch, ktorých plat je väčší ako ktorákoľvek z hodnôt v tomto

set (v skutočnosti viac ako minimálna hodnota - 12 000). Tento alternatívny spôsob možno považovať za prirodzenejší ako definovanie minimálnej mzdy v poddotazi. Ale v oboch prípadoch sa vygenerujú rovnaké výsledky vykonania dotazu, ktoré sú uvedené v tabuľke. 32 .

Tabuľka 32

Výsledok dotazu

personál č	fName	Volám sa	pozíciu	plat
SL21	John	biely	manažér	30000.00
SG14	David	Ford	Dozorca	18000.00
SG5	Susan	značka	manažér	24000.00

Príklad 23. Pomocou kľúčového slova ALL. Nájdite všetkých zamestnancov, ktorých mzda je väčšia ako mzda ktoréhokoľvek zamestnanca v pobočke firmy s číslom „chlast“.

VYBRAŤstaffNo, fName, INarae, pozícia, plat

ODpersonál

KDEplat > VŠETKY(VYBERTE plat

ODpersonál

KDEčíslo pobočky = "BOG3");

Vo všeobecnosti je tento dotaz podobný predchádzajúcemu. A v tomto prípade by bolo možné použiť poddotaz, ktorý určí maximálnu hodnotu mzdy pracovníkov oddelenia pod číslom „BOOS“, a následne externým dotazom vybrať informácie o všetkých zamestnancoch spoločnosti, ktorých mzda presahuje túto hodnotu. V tomto príklade je však zvolený prístup kľúčových slov VŠETKY. Výsledky vykonania dotazu sú uvedené v tabuľke. 33 .

Tabuľka 33

Výsledok dotazu

personál č	Volám sa	fName	pozíciu	plat
SL21	biely	John	manažér	30000,00

Dotazy s viacerými tabuľkami. Všetky vyššie uvedené príklady majú rovnaké dôležité obmedzenie: stĺpce umiestnené vo výslednej tabuľke sú vždy vybrané z jednej tabuľky. V mnohých prípadoch to však nestačí. Ak chcete zlúčiť stĺpce z niekoľkých zdrojových tabuliek vo výslednej tabuľke, musíte vykonať operáciu spojenia. V SQL sa operácia spojenia používa na spojenie informácií z dvoch tabuliek vytvorením párov súvisiacich riadkov vybraných z každej tabuľky. Páry riadkov umiestnené v kombinovanej tabuľke sa skladajú z rovnosti hodnôt špecifikovaných stĺpcov, ktoré sú v nich zahrnuté.

Ak potrebujete získať informácie z viacerých tabuliek, môžete použiť poddotaz alebo tabuľky spojiť. Ak výsledná tabuľka dotazu musí obsahovať stĺpce z rôznych zdrojových tabuliek, potom je vhodné použiť mechanizmus spájania tabuliek. Na vykonanie spojenia stačí zadať názvy dvoch alebo viacerých tabuliek v klauzule FROM, oddeliť ich čiarkami a potom do dotazu zahrnúť klauzulu WHERE s definíciou stĺpcov použitých na spojenie zadaných tabuliek. Okrem toho môžete namiesto názvov tabuliek použiť prezývky, im boli priradené v konštrukte FROM. V tomto prípade musia byť názvy tabuliek a k nim priradené aliasy oddelené medzerami. Aliasy možno použiť na kvalifikáciu názvov stĺpcov vždy, keď existuje nejednoznačnosť, do ktorej tabuľky konkrétny stĺpec patrí. Okrem toho je možné použiť aliasy na skrátenie názvov tabuliek. Ak je pre tabuľku definovaný alias, možno ho použiť kdekoľvek, kde sa vyžaduje názov tejto tabuľky.

Príklad 24. Jednoduché pripojenie. Urobte si menný zoznam všetkých klientov, ktorí si už prezreli aspoň jednu nehnuteľnosť na prenájom a vyjadrili svoj názor na túto záležitosť.

VYBRAŤc.clientNo, fName, Iname, propertyNo, comment

ODKlient c, Prehliadka v

KDEc.clientNo = v.clientNo;

Tento prehľad musí prezentovať informácie z tabuľky Klient aj z tabuľky zobrazenia, takže pri vytváraní dotazu použijeme mechanizmus spájania tabuliek. Konštrukt SELECT uvádza zoznam všetkých stĺpcov, ktoré by mali byť umiestnené vo výslednej tabuľke dotazu. Všimnite si, že stĺpec čísla klienta (clientNo) musí byť kvalifikovaný, pretože takýto stĺpec sa môže nachádzať aj v inej tabuľke, ktorá sa zúčastňuje spojenia. Preto je potrebné výslovne uviesť, o ktoré hodnoty tabuľky máme záujem. (V tomto príklade ste tiež mohli vybrať hodnoty stĺpca clientNo z tabuľky zobrazenia). Kvalifikácia názvu sa vykonáva tak, že pred názov stĺpca sa pridá názov príslušnej tabuľky (alebo jej alias). V našom príklade je hodnota "c" špecifikovaná ako alias pre tabuľku Klient. Na vytvorenie výsledných riadkov sa použijú tie riadky zdrojových tabuliek, ktoré majú rovnakú hodnotu v stĺpci clientNo. Táto podmienka je určená zadaním podmienky vyhľadávania c.clientNo=v.clientNo. Podobné stĺpce zdrojových tabuliek sa nazývajú zodpovedajúce stĺpce. Opísaná operácia je ekvivalentná operácii spája rovnosť relačná algebra. Výsledky vykonania dotazu sú uvedené v tabuľke. 34.

Tabuľka 34

Výsledok dotazu

klient č	fName	Volám sa	č	komentovať
CR56	Aline	Stewart	PG36
CR56	Aline	Stewart	PA14	príliš malá
CR56	Aline	Stewart	PG4
CR62	Mary	Tregear	PA14	žiadna jedáleň
CR76	John	Kay	PG4	príliš vzdialené

Dopyty s viacerými tabuľkami sa najčastejšie vykonávajú na dvoch tabuľkách spojených vzťahom jedna k mnohým (1:*) alebo vzťahom rodič-dieťa. Vo vyššie uvedenom príklade, ktorý zahŕňa prístup k tabuľkám Klient a Prezeranie, sú tieto tabuľky spojené práve takýmto vzťahom. Každý riadok Zobrazovacej tabuľky (podriadený) je priradený iba k jednému riadku Klientskej tabuľky (nadradenej), zatiaľ čo rovnaký riadok Klientskej tabuľky (nadradený) môže byť priradený.

s mnohými riadkami tabuľky zobrazenia (dieťa). Páry riadkov, ktoré sa vygenerujú pri vykonávaní dotazu, sú výsledkom všetkých platných kombinácií riadkov v podriadených a rodičovských tabuľkách. Časť 3.2.5 podrobne popisuje, ako v relačnej databáze primárne a cudzie kľúče tabuliek vytvárajú vzťah „rodič-dieťa“. Tabuľka obsahujúca cudzí kľúč je zvyčajne potomkom, zatiaľ čo tabuľka obsahujúca primárny kľúč bude vždy nadradená. Ak chcete použiť vzťah rodič-podriadený v dotaze SQL, musíte zadať podmienku vyhľadávania, ktorá porovnáva cudzí a primárny kľúč. Príklad 24 porovnáva primárny kľúč tabuľky Klient (v. clientNo) s cudzím kľúčom tabuľky Viewing (v. clientNo).

Štandard SQL navyše poskytuje nasledujúce spôsoby definovania daného pripojenia:

ODKlient s PRIPOJTE SA Prezeranie v ON c.clientNo = v.clientNo

ODKlient J OIN Prezeranie POUŽITÍM klient č

ODzákazník PRIRODZENÉ SPOJENIE Prezeranie

V každom prípade klauzula FROM nahrádza pôvodné klauzuly FROM a WHERE. V prvom prípade sa však vytvorí tabuľka s dvoma rovnakými stĺpcami clientNo, zatiaľ čo v ďalších dvoch prípadoch bude výsledná tabuľka obsahovať iba jeden stĺpec clientNo.

Príklad 25. Zoradiť výsledky spojenia tabuľky. Pre každú pobočku spoločnosti uveďte personálne čísla a mená zamestnancov, ktorí sú zodpovední za prípadné prenajaté priestory, a uveďte zariadenia pre

na ktoré odpovedajú.

VYBRAŤs.branchNo, s.staffNo, fName, INname, propertyNo

ODStaff s, PropertyForRent s

KDEs.staffNo = p.staffNo

ZORADIŤ PODĽAs.pobočka, s.staffNo, propertyNo;

Na uľahčenie čítania výsledkov sa výstup triedi pomocou čísla oddelenia ako hlavného kľúča triedenia a čísla personálu a čísla majetku ako vedľajších kľúčov. Výsledky vykonania dotazu sú uvedené v tabuľke. 35.

Tabuľka 35

Výsledok dotazu

pobočka č	StaffNo	fName	Volám sa	č
SZO	SG14	David	Ford	PG16
SZO	SG37	Ann	Buk	PG21
SZO	SG37	Ann	Buk	PG36
BOO5	SL41	Mary	Lee	PL94
SBI7	SA9	Julie	akože	PA14

Príklad 26. Spojenie troch stolov. Pri každej pobočke spoločnosti uveďte personálne čísla a mená zamestnancov, ktorí zodpovedajú za prípadné prenajaté objekty, vrátane mesta, v ktorom sa pobočka nachádza, a čísla objektov, za ktoré je každý zamestnanec zodpovedný.

VYBRAŤ b.pobočkaNo, b.city, s.staffNo, fName, INname, propertyNo

OD Pobočka b, Staff s, PropertyForRent s

KDEč. b.pobočky = č. č. pobočky A č. č. č. = č. č

ZORADIŤ PODĽA b.pobočka č, s.staffNo, propertyNo;

Výsledná tabuľka musí obsahovať stĺpce z troch zdrojových tabuliek – Branch, Staff a PropertyForRent – ​​takže dotaz musí tieto tabuľky spájať. Tabuľky Pobočka a Zamestnanci je možné spojiť pomocou podmienky b.Číslo pobočky=*s .Číslo pobočky, čím sa prepoja pobočky spoločnosti s pracovníkmi, ktorí v nich pracujú. Tabuľky Staff a PropertyForRent je možné spojiť pomocou podmienky s.staffNo=p.staffNo. V dôsledku toho bude každý pracovník spojený s prenajatými predmetmi, za ktoré je zodpovedný. Výsledky vykonania dotazu sú uvedené v tabuľke. 36.

Tabuľka 36

Výsledky dotazu

pobočka č	mesto	zamestnanciMo	fName	Volám sa	č
B003	Glasgow	SG14	David	Ford	PG16
B003	Glasgow	SG37	Ann	Buk	PG21
B003	Glasgow	SG37	Ann	Buk	PG36
B005	Londýn	SL41	Julie	Lee	PL94
B007	Aberdeen	SA9	Mary	akože	PA14

Všimnite si, že štandard SQL umožňuje alternatívnu formuláciu klauzúl FROM a WHERE:

OD(Pobočka b PRIPOJTE SA Zamestnanci POUŽÍVAJÚ pobočku č.) AS bs

PRIPOJTE SAPropertyForRent str POUŽITÍM personál č

Príklad 27. Zoskupenie podľa viacerých stĺpcov. Určte počet prenajatých predmetov, za ktoré je zodpovedný každý zamestnanec spoločnosti.

VYBRAŤč. pobočky, č. COUNT(*) AS počítať

OD Staff s, PropertyForRent s

KDE S.staffNo = p.staffNo

GROUP BYč. pobočky, č

ZORADIŤ PODĽAč. pobočky, č.

Pre vypracovanie požadovaného posudku je v prvom rade potrebné zistiť, ktorý zo zamestnancov spoločnosti zodpovedá za prenajaté predmety. Tento problém možno vyriešiť spojením tabuliek Staff a PropertyForRent v stĺpci staffNo v klauzulách FROM/WHERE. Potom je potrebné vytvoriť skupiny pozostávajúce z čísla pobočky a personálnych čísiel jej zamestnancov, pre ktoré má slúžiť konštrukcia GROUP BY. Nakoniec je potrebné výslednú tabuľku zoradiť pomocou klauzuly ORDER BY. Výsledky vykonania dotazu sú uvedené v tabuľke. 37.

Tabuľka 37

Výsledok dotazu

pobočka č	personál č	počítať
B003	SG14
B003	SG37
B005	SL41
B007	SA9

Vytváranie spojení. Spojenie je podmnožinou všeobecnejšej kombinácie údajov z dvoch tabuliek tzv karteziánsky. Kartézsky súčin dvoch tabuliek je ďalšia tabuľka pozostávajúca zo všetkých možných párov riadkov, ktoré sú súčasťou oboch tabuliek. Výsledná množina stĺpcov tabuľky obsahuje všetky stĺpce prvej tabuľky, za ktorými nasledujú všetky stĺpce druhej tabuľky. Ak zadáte dotaz na dve tabuľky bez zadania klauzuly WHERE, výsledkom dotazu v prostredí SQL bude karteziánsky súčin týchto tabuliek. Okrem toho štandard ISO poskytuje špeciálny formát pre príkaz SELECT, ktorý vám umožňuje vypočítať karteziánsky súčin dvoch tabuliek:

SELECT(* j zoznam stĺpcov]

FROM tableNamel CROSS JOINCayeUlte2

Uvažujme opäť o príklade, v ktorom sa prepojenie klienta a tabuľky na prezeranie vykonáva pomocou spoločného stĺpca clientNo Pri práci s tabuľkami, ktorých obsah je uvedený v tabuľke. 3.6 a 3.8, karteziánsky súčin týchto tabuliek bude mať 20 riadkov (4 riadky tabuľky Klient x 5 riadkov tabuľky zobrazenia = 20 riadkov). Je to ekvivalentné zadávaniu dotazu použitého v príklade 5-24, ale bez klauzuly WHERE. Postup na generovanie tabuľky obsahujúcej výsledky spojenia dvoch tabuliek pomocou príkazu SELECT je nasledovný.

1. Vytvorí sa karteziánsky súčin tabuliek špecifikovaných v konštrukcii FROM.

2. Ak dotaz obsahuje klauzulu WHERE, aplikujte podmienky vyhľadávania na každý riadok kartézskej tabuľky súčinov a do tabuľky uložte len tie riadky, ktoré spĺňajú dané podmienky. Z hľadiska relačnej algebry sa táto operácia nazýva obmedzenie karteziánsky súčin.

3. Pre každý zostávajúci riadok sa určí hodnota každého prvku zadaného v zozname SELECT, výsledkom čoho je samostatný riadok výslednej tabuľky.

4. Ak je v pôvodnom dotaze prítomný konštrukt SELECT DISTINCT, z výslednej tabuľky sa odstránia všetky duplicitné riadky.

5. Ak vykonávaný dotaz obsahuje klauzulu ORDER BY,

©2015-2019 stránka
Všetky práva patria ich autorom. Táto stránka si nenárokuje autorstvo, ale poskytuje bezplatné používanie.
Dátum vytvorenia stránky: 07.08.2016

o hodnotu stĺpca Disciplína . Dostaneme 4 skupiny, pre ktoré môžeme vypočítať nejaké skupinové hodnoty, ako je počet n-tic v skupine, maximálna alebo minimálna hodnota stĺpca Skóre. Tabuľka 5.7. Súhrnné funkcie

Funkcia	Výsledok
COUNT	Počet riadkov alebo neprázdnych hodnôt poľa, ktoré vybral dotaz
SUM	Súčet všetkých vybraných hodnôt daného poľa
AVG	Aritmetický priemer všetkých vybraných hodnôt v danom poli
MIN	Najmenšia zo všetkých vybratých hodnôt pre toto pole
MAX	Najväčšia zo všetkých vybratých hodnôt pre toto pole

R1
	Celé meno	Disciplína	stupňa
Skupina 1	Petrov F.I.	Databáza	5
	Sidorov K. A.	Databáza	4
	Mironov A.V.	Databáza	2
	Stepanova K.E.	Databáza	2
	Krylová T.S.	Databáza	5
	Vladimirov V.A.	Databáza	5
Skupina 2	Sidorov K. A.	Informačná teória	4
	Stepanova K.E.	Informačná teória	2
	Krylová T.S.	Informačná teória	5
	Mironov A.V.	Informačná teória	Nulový
Skupina 3	Trofimov P.A.	Siete a telekomunikácie	4
	Ivanova E.A.	Siete a telekomunikácie	5
	Utkina N.V.	Siete a telekomunikácie	5
Skupina 4	Vladimirov V.A.	anglický jazyk	4
	Trofimov P.A.	anglický jazyk	5
	Ivanova E.A.	anglický jazyk	3
	Petrov F.I.	anglický jazyk	5

Súhrnné funkcie sa používajú ako názvy polí v príkaze SELECT, s jednou výnimkou: berú názov poľa ako argument. S funkciami SUM a AVG možno použiť iba číselné polia. S funkciami COUNT , MAX a MIN možno použiť numerické aj znakové polia. Pri použití so znakovými poľami ich MAX a MIN preložia do ich ekvivalentu ASCII a spracujú ich v abecednom poradí. Niektoré DBMS umožňujú vnorené agregáty, ale toto je odklon od štandardu ANSI so všetkými jeho dôsledkami.

Môžete napríklad vypočítať počet študentov, ktorí vykonali skúšky v jednotlivých disciplínach. Ak to chcete urobiť, musíte vykonať dotaz zoskupený podľa poľa „Predmet“ a ako výsledok zobraziť názov disciplíny a počet riadkov v skupine pre túto disciplínu. Použitie znaku * ako argumentu funkcie COUNT znamená spočítať všetky riadky v skupine.

VYBERTE R1.Disciplína, COUNT(*) ZO SKUPINY R1 PODĽA R1.Disciplína

výsledok:

Ak chceme spočítať počet študentov, ktorí zložili skúšku v ktorejkoľvek disciplíne, musíme pred zoskupením vylúčiť z pôvodného pomeru nulové hodnoty. V tomto prípade bude žiadosť vyzerať takto:

Dostaneme výsledok:

V tomto prípade linka so študentom

Mironov A.V.	Informačná teória	Nulový

nebude spadať do množiny n-tic pred zoskupením, takže počet n-tic v skupine pre disciplínu " Informačná teória“ bude o 1 menej.

Dá sa aplikovať agregované funkcie aj bez operácie predbežného zoskupenia, v takom prípade sa celý vzťah považuje za jednu skupinu a pre túto skupinu možno vypočítať jednu hodnotu na skupinu.

Opätovne s odkazom na databázu "Session" (tabuľky R1, R2, R3) zistíme počet úspešne absolvovaných skúšok:

Toto sa samozrejme líši od výberu poľa, pretože vždy sa vráti jedna hodnota, bez ohľadu na to, koľko riadkov je v tabuľke. Argumentovať agregované funkcie môžu existovať samostatné stĺpce tabuliek. Ak však chcete vypočítať napríklad počet rôznych hodnôt určitého stĺpca v skupine, musíte spolu s názvom stĺpca použiť kľúčové slovo DISTINCT. Vypočítajme počet rôznych známok získaných v každej disciplíne:

výsledok:

Výsledok môže zahŕňať hodnotu poľa zoskupenia a niekoľko agregované funkcie a v podmienkach zoskupovania je možné použiť viacero polí. V tomto prípade sa skupiny vytvárajú podľa množiny špecifikovaných zoskupovacích polí. Súhrnné operácie možno použiť na spojenie viacerých zdrojových tabuliek. Položme si napríklad otázku: určte pre každú skupinu a každú disciplínu počet tých, ktorí úspešne zložili skúšku, a priemerné skóre za disciplínu.

výsledok:

Nemôžeme použiť agregované funkcie v klauzule WHERE, pretože predikáty sa vyhodnocujú z hľadiska jedného riadku a agregované funkcie- pokiaľ ide o skupiny riadkov.

Klauzula GROUP BY vám umožňuje definovať podmnožinu hodnôt v konkrétnom poli z hľadiska iného poľa a použiť na podmnožinu súhrnnú funkciu. To umožňuje kombinovať polia a agregované funkcie v jedinej klauzule SELECT. Súhrnné funkcie možno použiť ako vo výstupnom vyjadrení výsledkov riadku SELECT, tak aj pri vyjadrení podmienky spracovania pre vytvorené skupiny HAVING . V tomto prípade sa každá agregovaná funkcia vypočíta pre každú vybranú skupinu. Hodnoty vyplývajúce z výpočtu agregované funkcie, možno použiť na zobrazenie zodpovedajúcich výsledkov alebo na podmienenie výberu skupín.

Poďme zostaviť dotaz, ktorý zobrazí skupiny, v ktorých bolo prijatých viac ako jedna dvojka v jednej disciplíne na skúškach:

V budúcnosti budeme ako príklad pracovať nie s databázou „Session“, ale s databázou „Bank“ pozostávajúcou z jednej tabuľky F , v ktorej je uložený vzťah F obsahujúci informácie o účtoch v pobočkách určitej banky:

F = (N, celé meno, pobočka, dátum otvorenia, dátum uzavretia, zostatok); Q = (pobočka, mesto);

pretože na tomto základe je možné názornejšie ilustrovať prácu s agregovanými funkciami a zoskupovaním.

Predpokladajme napríklad, že chceme zistiť celkový stav účtov na pobočkách. Pre každú z nich môžete vytvoriť samostatný dotaz výberom SUM(Zostatok) z tabuľky pre každú vetvu. GROUP BY ich však umiestni do jedného príkazu:

SELECT Branch, SUM(Balance) FROM F GROUP BY Branch;

Platí GROUP BY agregované funkcie nezávisle pre každú skupinu definovanú hodnotou poľa Pobočka. Skupina pozostáva z riadkov s rovnakou hodnotou v poli Pobočka a

• Súhrnné funkcie sa používajú ako názvy polí v príkaze SELECT, s jednou výnimkou: berú názov poľa ako argument. S funkciami SUM a AVG možno použiť iba číselné polia. S funkciami COUNT, MAX a MIN možno použiť číselné aj znakové polia. Pri použití so znakovými poľami MAX a MIN preloží ich do ich ASCII ekvivalentu a spracuje ich v abecednom poradí. Niektoré DBMS umožňujú vnorené agregáty, ale toto je odklon od štandardu ANSI so všetkými jeho dôsledkami.

Môžete napríklad vypočítať počet študentov, ktorí vykonali skúšky v jednotlivých disciplínach. Ak to chcete urobiť, musíte vykonať dotaz zoskupený podľa poľa „Predmet“ a zobraziť ako výsledok názov disciplíny a počet riadkov v skupine pre túto disciplínu. Použitie znaku * ako argumentu funkcie COUNT znamená spočítať všetky riadky v skupine.

VYBERTE R1. Disciplína, COUNT(*)

GROUP PODĽA R1. Disciplína;

výsledok:

SELECT R1.Discipline, COUNT (*)

KDE R1. Hodnotenie NIE JE NULL

GROUP PODĽA R1. Disciplína;

výsledok:

nepadne do množiny n-tic pred zoskupením, takže počet n-tic v skupine pre disciplínu „Teória informácií“ bude o 1 menší.

Podobný výsledok možno získať, ak je dotaz napísaný nasledujúcim spôsobom:

VYBERTE R1. Disciplína, COUNT(R1. Skóre)

SKUPINA PODĽA R1. Disciplína;

Funkcia COUNT (NÁZOV ATRIBUTU) počíta počet definovaných hodnôt v skupine, na rozdiel od funkcie COUNT(*), ktorý počíta počet riadkov v skupine. V skupine s disciplínou „Teória informácií“ budú skutočne 4 riadky, ale iba 3 určité hodnoty atribútu „Hodnotenie“.

Pravidlá pre spracovanie hodnôt NULL v agregačných funkciách

Ak sú niektoré hodnoty v stĺpci rovnaké NULOVÝ pri výpočte výsledku funkcie sú vylúčené.

Ak sú všetky hodnoty v stĺpci rovnaké NULOVÝ, potom Max. min. súčet priem = NULL, počet = 0 (nula).

Ak je stôl prázdny, počet (*) = 0 .

Agregátne funkcie možno použiť aj bez operácie predbežného zoskupenia, v takom prípade sa celý vzťah považuje za jednu skupinu a pre túto skupinu možno vypočítať jednu hodnotu na skupinu.

Pravidlá pre interpretáciu agregovaných funkcií

Agregátne funkcie môžu byť zahrnuté do zoznamu výstupov a potom sú aplikované na celú tabuľku.

VYBERTE MAX (Skóre) z R1 na relácii dá maximálnu známku;

VYBERTE SÚČET (Skóre) z R1 poskytne súčet všetkých hodnotení relácie;

VYBERTE AVG (Skóre) z R1 poskytne priemerné skóre za celú reláciu.

2; Výsledok: "width="640"

Ak sa opäť pozrieme na databázu „Session“ (tabuľky R1), zistíme počet úspešne zložených skúšok:

VYBRAŤ POČET (*) Prenajaté _ skúšok

WHERE skóre 2;

výsledok:

Agregátne funkcie môžu brať jednotlivé stĺpce tabuliek ako argumenty. Aby ste mohli vypočítať napríklad počet odlišných hodnôt pre určitý stĺpec v skupine, musíte spolu s názvom stĺpca použiť kľúčové slovo DISTINCT. Vypočítajme počet rôznych známok získaných v každej disciplíne:

SELECT R1.Discipline, COUNT (ODLIŠNÁ R1.Skóre)

KDE R1. Hodnotenie NIE JE NULL

GROUP PODĽA R1. Disciplína;

výsledok:

Rovnaký výsledok sa získa, ak sa vylúči explicitná podmienka v časti WHERE, v takom prípade bude dotaz vyzerať takto:

VYBERTE R1. Disciplína, POČET (DISTINCT R1. Skóre)

SKUPINA PODĽA R1. Disciplína;

Funkcia POČET (ODLIŠNÉ R1. Skóre) počíta len isté rôzne hodnoty.

Aby sa v tomto prípade dosiahol požadovaný výsledok, je potrebné vykonať predbežnú konverziu typu údajov stĺpca „Skóre“ a uviesť ho do skutočného typu, potom výsledkom výpočtu priemeru nebude celé číslo. V tomto prípade bude žiadosť vyzerať takto:

2 Zoskupiť podľa R2. Skupina, R1. Disciplína; Tu funkcia CAST() skonvertuje stĺpec Skóre na platný typ údajov. "width="640"

Vyberte R2.Group, R1.Subject,Count(*) ako Total, AVG(cast(Score as decimal(3,1))) ako Average_score

Od R1, R2

kde R1. Celé meno = R2. Celé meno a R1. hodnotenie nie je nulové

a R1. 2. ročník

Zoskupiť podľa R2. Skupina, R1. Disciplína;

Tu je funkcia CAST() skonvertuje stĺpec Skóre na platný typ údajov.

V klauzule WHERE nemôžete použiť agregačné funkcie, pretože podmienky v tejto časti sa vyhodnocujú z hľadiska jedného riadka, zatiaľ čo agregované funkcie sa vyhodnocujú z hľadiska skupín riadkov.

Klauzula GROUP BY vám umožňuje definovať podmnožinu hodnôt v konkrétnom poli z hľadiska iného poľa a použiť na podmnožinu súhrnnú funkciu. To umožňuje kombinovať polia a agregačné funkcie do jednej klauzuly SELECT. Agregačné funkcie je možné použiť ako vo výstupnom vyjadrení výsledkov riadku SELECT, tak aj pri vyjadrení podmienky spracovania vytvorených skupín HAVING. V tomto prípade sa každá agregovaná funkcia vypočíta pre každú vybranú skupinu. Hodnoty získané pri výpočte agregovaných funkcií možno použiť na zobrazenie zodpovedajúcich výsledkov alebo pre podmienky výberu skupiny.

Vytvorme dotaz, ktorý zobrazí skupiny, v ktorých bolo v rámci skúšok prijatých viac ako jedna dvojka v jednej disciplíne:

jeden; Výsledok: "width="640"

VYBERTE R2. Skupina

OD R1,R2

KDE R1. Celé meno = R2. Celé meno A

R1. Skóre = 2

SKUPINA PODĽA R2.Skupina, R1.Disciplína

HAVING count(*) 1;

výsledok:

Máme databázu "Banka", pozostávajúcu z jednej tabuľky F, v ktorej je uložený vzťah F, obsahujúci informácie o účtoch v pobočkách určitej banky:

Zistite celkový zostatok na účtoch v pobočkách. Pre každú z nich môžete vytvoriť samostatný dotaz výberom položky SUM (Zostatok) z tabuľky pre každú vetvu, ale operácia GROUP BY ich všetky umiestni do jedného príkazu:

VYBRAŤ Pobočka , SUM( Zvyšok )

SKUPINA PODĽA pobočky;

GROUP BY aplikuje agregované funkcie nezávisle pre každú skupinu identifikovanú hodnotou poľa Pobočka. Skupinu tvoria riadky s rovnakou hodnotou v poli Pobočka a funkcia SUM sa uplatňuje samostatne pre každú takúto skupinu, t. j. celkový zostatok účtu sa počíta samostatne pre každú pobočku. Hodnota poľa, na ktoré sa vzťahuje GROUP BY, má podľa definície iba jednu hodnotu na výstupnú skupinu, rovnako ako výsledok agregovanej funkcie.

5000; Argumenty v klauzule HAVING sa riadia rovnakými pravidlami ako v klauzule SELECT, kde sa používa GROUP BY. Musia mať jednu hodnotu na výstupnú skupinu. "width="640"

Predpokladajme, že vyberiete len tie pobočky, ktorých celkové zostatky na účtoch sú väčšie ako 5 000 USD, ako aj celkové zostatky pre vybrané pobočky. Ak chcete zobraziť pobočky s celkovými zostatkami nad 5 000 USD, použite klauzulu HAVING. Klauzula HAVING špecifikuje kritériá použité na odstránenie určitých skupín z výstupu, rovnako ako klauzula WHERE pre jednotlivé riadky.

Správny príkaz by bol:

SELECT vetva, SUM(zostatok)

GROUP BY Pobočka

MAJÚCI SÚČET ( Zvyšok ) 5 000;

Argumenty vo vete MAJÚCE podliehajú rovnakým pravidlám ako v návrhu VYBRAŤ kde sa používa GROUP BY. Musia mať jednu hodnotu na výstupnú skupinu.

Nasledujúci príkaz bude zakázaný:

SELECT Branch,SUM(Zostatok)

SKUPINA PODĽA pobočky

HAVING Dátum otvorenia = 27/12/2004 ;

Lúka Dátum otvorenia nemožno použiť vo vete MAJÚCE, pretože môže mať viac ako jednu hodnotu na výstupnú skupinu. Aby sa predišlo tejto situácii, návrh MAJÚCE by sa mali vzťahovať iba na vybrané agregáty a polia GROUP BY. Existuje správny spôsob, ako podať vyššie uvedenú žiadosť:

SELECT Branch,SUM(Zostatok)

WHERE OpenDate = '27/12/2004'

SKUPINA PODĽA pobočky;

Význam tohto dotazu je nasledovný: nájsť súčet zostatkov pre každú pobočku účtov otvorených 27. decembra 2004.

Ako už bolo uvedené, HAVING môže prevziať iba argumenty, ktoré majú jednu hodnotu na výstupnú skupinu. V praxi sú najčastejšie odkazy na agregované funkcie, ale platné sú aj polia vybrané pomocou GROUP BY. Chceme napríklad vidieť celkové zostatky na účtoch pobočiek v Petrohrade, Pskove a Uryupinsku:

SELECT vetva, SUM(zostatok)

OD F,Q

KDE F. Pobočka = Q. Pobočka

SKUPINA PODĽA pobočky

MAJÚCI pobočku ('Petrohrad', 'Pskov', 'Uryupinsk');

100 000; Ak je celkový zostatok viac ako 100 000 $, potom to uvidíme vo výslednom vzťahu, inak dostaneme prázdny vzťah. "width="640"

Preto v aritmetických výrazoch predikátov zahrnutých do výberovej klauzuly klauzuly HAVING možno priamo použiť iba špecifikácie stĺpcov špecifikovaných ako zoskupovacie stĺpce v klauzule GROUP BY. Zostávajúce stĺpce možno zadať iba v rámci špecifikácií agregačných funkcií COUNT, SUM, AVG, MIN a MAX, ktoré v tomto prípade vypočítajú určitú súhrnnú hodnotu pre celú skupinu riadkov. Výsledkom vykonania sekcie HAVING je zoskupená tabuľka obsahujúca len tie skupiny riadkov, pre ktoré je výsledok výpočtu podmienky výberu v časti HAVING PRAVDA. Najmä, ak je klauzula HAVING prítomná v dotaze, ktorý neobsahuje GROUP BY, výsledkom jej vykonania je buď prázdna tabuľka, alebo výsledok vykonania predchádzajúcich sekcií tabuľkového výrazu, ktorý sa považuje za jednu skupinu. bez zoskupovania stĺpcov. Zvážte príklad. Povedzme, že chceme zobraziť celkovú sumu zostatkov pre všetky pobočky, ale iba ak je vyššia ako 100 000 USD. V tomto prípade náš dotaz nebude obsahovať operáciu zoskupovania, ale bude obsahovať sekciu HAVING a bude vyzerať takto:

VYBERTE SÚČET( Zvyšok )

HAVING SUM( Zvyšok ) 100 000;

Ak je celkový zostatok viac ako 100 000 $, potom to uvidíme vo výslednom vzťahu, inak dostaneme prázdny vzťah.