MS SQL v příkladech: Poddotazy v příkladech

C# .NET Databáze MS SQL v příkladech: Poddotazy v příkladech

Minule jsme si vyzkoušeli dotazy přes více tabulek, dnes se zaměříme na poddotazy. Výsledek SQL dotazu totiž může velmi jednoduše sloužit jako zdroj pro další SELECT. Buďte však opatrní, protože se jedná o časově náročné operace a poddotazy jsou obecně velmi pomalé.

Opět jsem se příklady snažil seřadit dle jejich složitosti.

Příklad 1: Vypsání článků, jejichž autorem je uživatel s přezdívkou David.

SELECT
        Titulek,
        Perex,
        Obsah
FROM Clanek
WHERE AutorID = (
        SELECT
                UzivatelID
        FROM Uzivatel
        WHERE Nick = 'David'
);

Příklad 2: Zjištění průměrného počtu článků na uživatele.

SELECT
        AVG(X)
FROM
(
        SELECT
                COUNT(*) X
        FROM Clanek
        GROUP BY (AutorID)
) a;

Příklad 3: Zjistit průměrný výsledek ze zadaného testu.

SELECT
        AVG(Skore) Vysledek
FROM
(
        SELECT
                Skore
        FROM VysledekTestu
        WHERE (TestID = 1)
) a;

Příklad 4: Zjistit průměrný výsledek ze všech testů na portálu.

SELECT
        AVG(Skore) Vysledek
FROM
(
        SELECT
                Skore
        FROM VysledekTestu
) a;

Příklad 5: Jaký je nejlepší průměrný výsledek v testech.

SELECT
        MAX(X) Vysledek
FROM (
        SELECT
                AVG(Skore) X
        FROM (
                SELECT
                        Skore, UzivatelID
                FROM VysledekTestu
        ) a
        GROUP BY (UzivatelID)
) b;

Příklad 6: Vypsání článků, které nepatří do žádné sekce.

SELECT
        c.ClanekID,
        c.Titulek
FROM Clanek c
WHERE
        (SELECT
                COUNT(*)
         FROM ClanekSekce
         WHERE (ClanekSekce.ClanekID = c.ClanekID)) = 0;

Příklad 7: Vybrat uživatele, kteří mají nejlepší výsledek z testu s určitým ID (díky poddotazu můžeme vybrat více hodnot).

SELECT
        Nick,
        Skore
FROM VysledekTestu, Uzivatel
WHERE (VysledekTestu.Skore=(
        SELECT
                MAX(Skore)
        FROM VysledekTestu
        WHERE TestID = 1)) AND
        (TestID = 1) AND
        (VysledekTestu.UzivatelID = Uzivatel.UzivatelID);

Příklad 8: Vypsání komentářů, které jsou novější, než všechny komentáře autora s danou přezdívkou (slouží uživatelům k zobrazení komentářů, které ještě nečetli od své poslední aktivity na webu, tedy od napsání svých komentářů).

SELECT
        Obsah,
        Datum
FROM Komentar
WHERE Datum > ALL (
        SELECT Datum
    FROM Komentar
                JOIN Uzivatel
                        ON (Uzivatel.UzivatelID = Komentar.UzivatelID)
    WHERE (Uzivatel.Nick = 'Denny')
)
ORDER BY Datum DESC;

Příklad 9: Který uživatel napsal nejvyšší počet článků.

SELECT TOP 1
U.Nick, (
        SELECT
                COUNT(*)
        FROM Clanek
        WHERE (Clanek.AutorID = U.UzivatelID)
) CNT
FROM
Uzivatel U
ORDER BY CNT DESC;

Příklad 10: Který uživatel má nejlepší průměrný výsledek v testech.

SELECT TOP 1
        U.Nick, (
        SELECT
                AVG(Skore)
        FROM (
                SELECT
                        Skore
                FROM VysledekTestu
                WHERE UzivatelID = U.UzivatelID
        ) X
        ) Vysledek
FROM Uzivatel U
ORDER BY Vysledek DESC;

Pohledy

Příklad 11: Rozhodli jsme se uložit si příkaz na vypsání nejnovějšího článku do pohledu.

CREATE VIEW NejnovejsiClanek AS
        SELECT TOP 1
                c.Perex,
                c.PrettyURL,
                c.Titulek,
                c.Publikovano,
                u.Nick
        FROM Clanek c, Uzivatel u
        WHERE (u.UzivatelID = c.AutorID)
        ORDER BY c.Publikovano DESC;

Následně budeme volat jen zkráceně:

SELECT *
FROM NejnovejsiClanek

Další dotazy

Následující dotazy jsem nechával na konec, protože mění obsah nebo strukturu databáze a mohly by zasahovat do předchozích příkladů. Udělejme si je tedy nyní:

Příklad 12: Občas je třeba vymazat nevhodný komentář.

DELETE FROM
        Komentar
WHERE
        KomentarID = 5;

Příklad 13: Ukázalo se jako potřebné přidat k článkům hodnocení (podobné, jako like na facebooku).

ALTER TABLE Clanek ADD Palcu int;

Příklad 14: Pro obnovu tabulky komentáře ze zálohy (kvůli útoku spambotů) se musí tabulka nejprve vymazat, proto byl dodán následující dotaz.

DROP TABLE Komentar;

Příště si řekneme něco o optimalizaci MS SQL databáze.


 

  Aktivity (1)

Článek pro vás napsal David Čápka
Avatar
Autor pracuje jako softwarový architekt a pedagog na projektu ITnetwork.cz (a jeho zahraničních verzích). Velmi si váží svobody podnikání v naší zemi a věří, že když se člověk neštítí práce, tak dokáže úplně cokoli.
Unicorn College Autor se informační technologie naučil na Unicorn College - prestižní soukromé vysoké škole IT a ekonomie.

Jak se ti líbí článek?
Celkem (5 hlasů) :
3.63.63.63.6 3.6


 



 

 

Komentáře

Avatar
JOF
Tým ITnetwork
Avatar
JOF:

Ahoj,
poddotazy v jazyku SQL jsou určitě mocný nástroj,
ale k některým zde uvedeným příkladům se mi vůbec nehodí.
Příklad 4 by se dal řešit jednoduchým dotazem:
SELECT Avg(Skore)
FROM VysledekTestu

a stejně tak příklad 3:
SELECT Avg(Skore)
FROM VysledekTestu
WHERE (TestID = 1)

(podobně bychom si jeden SELECT mohli odpustit v příkladu 10)

Ani příklad 1 by se nemusel řešit s pomocí poddotazu, ale třeba spojením tabulek s pomocí JOIN. Zkuste sami ... ;-)

 
Odpovědět 27.12.2013 0:32
Avatar
rt
Člen
Avatar
rt:

Ahoj, priklad 2 je podle me vyresen spatne. Zda se mi, ze neuvazuje uzivatele bez clanku.

Napsal jsem takovyto dotaz

SELECT AVG(Cast(Y.Pocet as float)) FROM

(SELECT Uzivatel.Nick, COUNT (Clanek.ClanekID) Pocet
FROM Uzivatel LEFT JOIN Clanek ON (Clanek.AutorID = Uzivatel.Uziva­telID)
GROUP BY Uzivatel.Nick) Y

Vraci mi to spravna cisla. Taky resi problem s pretypovanim na float (prumer nemusi byt cele cislo, zvlaste s tak malou databazi s jakou pracujeme v prikladech).

 
Odpovědět 23.2.2015 19:51
Avatar
Odpovídá na rt
Michal Štěpánek:

Proč uvádíš Pocet as float? Copak počet uživatelů může být desetinné číslo?
Edit: kdybych si to přečetl do konce nebyl bych za vola...

Editováno 24.2.2015 8:37
Odpovědět 24.2.2015 8:35
Nikdy neříkej nahlas, že to nejde. Vždycky se totiž najde blbec, který to neví a udělá to...
Avatar
rt
Člen
Avatar
Děláme co je v našich silách, aby byly zdejší diskuze co nejkvalitnější. Proto do nich také mohou přispívat pouze registrovaní členové. Pro zapojení do diskuze se přihlas. Pokud ještě nemáš účet, zaregistruj se, je to zdarma.

Zobrazeno 4 zpráv z 4.