V SQL je klavzula WITH znana tudi kot CTE. Je zmogljiva funkcija, ki nam omogoča ustvarjanje začasnih nizov rezultatov znotraj poizvedbe. Ena glavnih vlog CTE-jev je poenostavitev kompleksnih poizvedb v manjše podpoizvedbe, ki jih je mogoče ponovno uporabiti. To pripomore k temu, da je koda dolgoročno bolj berljiva in vzdržljiva.
Pridružite se nam v tej vadnici, ko raziskujemo delovanje skupnih tabelnih izrazov z uporabo klavzule WITH in podprte funkcionalnosti.
Zahteve:
Za demonstracijske namene bomo uporabili naslednje:
- MySQL različica 8.0 in novejša
- Vzorčna zbirka podatkov Sakila
Ko so dane zahteve izpolnjene, lahko nadaljujemo z učenjem več o CTE in klavzuli WITH.
SQL WITH klavzula
Klavzula WITH nam omogoča, da definiramo enega ali več začasnih nizov rezultatov, ki so znani kot splošni tabelni izrazi.
Na dobljene CTE-je se lahko sklicujemo v glavni poizvedbi kot v kateri koli drugi tabeli ali nizu rezultatov. To igra ključno vlogo pri ustvarjanju modularnih poizvedb SQL.
Čeprav se sintaksa CTE lahko nekoliko razlikuje glede na vaše zahteve, je spodaj prikazana osnovna sintaksa CTE v SQL:
WITH cte_name (stolpec1, stolpec2, ...) AS (
-- Poizvedba CTE
IZBERI ...
OD ...
KJE ...
)
-- Glavna poizvedba
IZBERI ...
OD ...
PRIDRUŽITE SE cte_name NA ...
KJE ...
Začnemo s ključno besedo WITH, ki bazi podatkov SQL pove, da želimo ustvariti in uporabiti CTE.
Nato določimo ime za CTE, ki nam omogoča, da se nanj sklicujemo v drugih poizvedbah.
Določimo tudi izbirni seznam imen stolpcev, če CTE vključuje vzdevke stolpcev.
Nato nadaljujemo z definiranjem poizvedbe CTE. Vsebuje vse naloge ali podatke, ki jih izvaja CTE, v oklepajih.
Na koncu določimo glavno poizvedbo, ki se sklicuje na CTE.
Primer uporabe:
Eden najboljših načinov za razumevanje uporabe in dela s CTE je ogled praktičnega primera.
Vzemimo za primer vzorčno bazo podatkov Sakila. Recimo, da želimo najti 10 najboljših strank z največjim številom najemnin.
Oglejte si naslednji prikazani CTE.
Uporaba klavzule SQL WITH za iskanje najboljših 10 strank z najvišjim številom najemnin:
Z CustomerRentals AS (IZBERI c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS rental_count
OD stranke c
PRIDRUŽITE SE najemu r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
)
IZBERI *
OD CustomerRentals
VRSTI PO rental_count DESC
OMEJITEV 10;
V danem primeru začnemo z definiranjem novega CTE s ključno besedo WITH, ki ji sledi ime, ki ga želimo dodeliti CTE. V tem primeru ga imenujemo »CustomerRentals«.
Znotraj telesa CTE izračunamo število najemnin za vsako stranko tako, da združimo tabelo strank in najemnin.
Nazadnje v glavni poizvedbi izberemo vse stolpce iz CTE, razporedimo rezultate glede na število najemnin (padajoči vrstni red) in omejimo izhod na samo prvih 10 vrstic.
To nam omogoča, da pridobimo stranke z največjim številom najemnin, kot je prikazano v naslednjem rezultatu:
Rekurzivni CTE
V nekaterih drugih primerih imate morda opravka s hierarhičnimi strukturami podatkov. Tukaj pridejo v poštev rekurzivni CTE.
Vzemimo za primer primer, ko želimo krmariti po hierarhični organizaciji ali predstaviti drevesno strukturo. Za ustvarjanje rekurzivnega CTE lahko uporabimo ključno besedo WITH RECURSIVE.
Ker v bazi podatkov Sakila ni hierarhičnih podatkov, ki bi jih lahko uporabili za prikaz rekurzivnega CTE, nastavimo osnovni primer.
USTVARI TABELO oddelek (Department_id INT PRIMARY KEY AUTO_INCREMENT,
ime_oddelka VARCHAR(255) NI NULL,
parent_department_id INT,
TUJI KLJUČ (parent_department_id) REFERENCE oddelek (department_id)
);
INSERT INTO oddelek (department_name, parent_department_id)
VREDNOTE
('Podjetje', NULL),
('Finance', 1),
('HR', 1),
('Računovodstvo', 2),
'Zaposlovanje', 3),
('Plačne liste', 4);
V tem primeru imamo vzorčno tabelo »oddelka« z nekaj naključnimi podatki. Za iskanje hierarhične strukture oddelkov lahko uporabimo rekurzivni CTE, kot sledi:
Z REKURZIVNO hierarhijo oddelkov AS (IZBERITE department_id, department_name, parent_department_id
IZ oddelka
KJER JE parent_department_id NULL
UNION VSI
IZBERITE d.department_id, d.department_name, d.parent_department_id
IZ oddelka d
PRIDRUŽITE SE Hierarhiji oddelka dh ON d.parent_department_id = dh.department_id
)
IZBERI *
FROM DepartmentHierarchy;
V tem primeru se rekurzivni CTE začne z oddelki, ki imajo NULL »parent_department_id« (korenski oddelki) in rekurzivno pridobi podrejene oddelke.
Zaključek
V tej vadnici smo spoznali najbolj temeljne in uporabne funkcije v bazah podatkov SQL, kot so skupni izrazi tabele, tako da smo razumeli, kako delati s ključno besedo WITH.