U SQL-u klauzula WITH također je poznata kao CTE. To je moćna značajka koja nam omogućuje stvaranje privremenih skupova rezultata unutar upita. Jedna od glavnih uloga CTE-ova je pojednostavljenje složenih upita u manje podupite koji se mogu ponovno koristiti. To pomaže da kod bude čitljiviji i dugoročno održiviji.
Pridružite nam se u ovom vodiču dok istražujemo rad zajedničkih tabličnih izraza koristeći klauzulu WITH i podržanu funkcionalnost.
Zahtjevi:
U svrhu demonstracije koristit ćemo sljedeće:
- MySQL verzija 8.0 i novije
- Sakila uzorak baze podataka
S ispunjenjem zadanih zahtjeva, možemo nastaviti s učenjem više o CTE-ovima i klauzuli WITH.
SQL WITH klauzula
Klauzula WITH nam omogućuje da definiramo jedan ili više privremenih skupova rezultata koji su poznati kao izrazi zajedničke tablice.
Možemo referencirati rezultirajuće CTE-ove u glavnom upitu kao u bilo kojoj drugoj tablici ili skupu rezultata. Ovo igra ključnu ulogu u stvaranju modularnih SQL upita.
Iako se sintaksa CTE-a može malo razlikovati ovisno o vašim zahtjevima, sljedeće prikazuje osnovnu sintaksu CTE-a u SQL-u:
SA cte_name (stupac1, stupac2, ...) AS (
-- CTE upit
IZABERI ...
OD...
GDJE ...
)
-- Glavni upit
IZABERI ...
OD...
PRIDRUŽITE SE cte_name NA...
GDJE ...
Počinjemo s ključnom riječi WITH koja govori SQL bazi podataka da želimo stvoriti i koristiti CTE.
Zatim navodimo naziv za CTE koji nam omogućuje da ga referenciramo u drugim upitima.
Također navodimo izborni popis naziva stupaca ako CTE uključuje pseudonime stupaca.
Zatim nastavljamo s definiranjem CTE upita. Sadrži sve zadatke ili podatke koje CTE provodi u zagradama.
Na kraju, navodimo glavni upit koji se odnosi na CTE.
Primjer upotrebe:
Jedan od najboljih načina da shvatite kako koristiti i raditi s CTE-ovima je pogledati praktični primjer.
Uzmimo za primjer Sakila uzorak baze podataka. Pretpostavimo da želimo pronaći prvih 10 kupaca s najvećim brojem iznajmljivanja.
Pogledajte sljedeći prikazani CTE.
Korištenje SQL WITH klauzule za pronalaženje prvih 10 kupaca s najvećim brojem najmova:
S CustomerRentals AS (SELECT c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS rental_count
OD kupca c
PRIDRUŽITE SE najmu r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
)
IZABERI *
OD CustomerRentals
REDAJ PO rental_count DESC
OGRANIČENJE 10;
U navedenom primjeru započinjemo definiranjem novog CTE-a pomoću ključne riječi WITH nakon koje slijedi naziv koji želimo dodijeliti CTE-u. U ovom slučaju to zovemo 'CustomerRentals'.
Unutar CTE tijela izračunavamo broj najma za svakog kupca spajanjem tablice kupaca i najmova.
Na kraju, u glavnom upitu odabiremo sve stupce iz CTE-a, poredamo rezultate na temelju broja najma (silazni redoslijed) i ograničavamo izlaz samo na prvih 10 redaka.
To nam omogućuje da dohvatimo klijente s najvećim brojem najmova kao što je prikazano u sljedećem rezultatu:
Rekurzivni CTE
U nekim drugim slučajevima možda imate posla s hijerarhijskim strukturama podataka. Ovdje rekurzivni CTE stupaju na scenu.
Uzmimo za primjer slučaj u kojem se želimo kretati hijerarhijskom organizacijom ili prikazati strukturu poput stabla. Možemo koristiti ključnu riječ WITH RECURSIVE za stvaranje rekurzivnog CTE-a.
Budući da ne postoje hijerarhijski podaci koje možemo koristiti u Sakila bazi podataka da pokažemo rekurzivni CTE, postavimo osnovni primjer.
STVARANJE TABLICE odjel (department_id INT PRIMARNI KLJUČ AUTO_INKREMENT,
naziv_odsjeka VARCHAR(255) NOT NULL,
parent_department_id INT,
FOREIGN KEY (parent_department_id) REFERENCE odjel(department_id)
);
INSERT INTO odjel (department_name, parent_department_id)
VRIJEDNOSTI
('Korporativno', NULL),
('Financije', 1),
('HR', 1),
('Računovodstvo', 2),
'Regrutiranje', 3),
('Obračun plaća', 4);
U ovom slučaju imamo primjer tablice 'odjel' s nekim nasumičnim podacima. Da bismo pronašli hijerarhijsku strukturu odjela, možemo koristiti rekurzivni CTE na sljedeći način:
S REKURZIVNOM Hijerarhijom odjela AS (SELECT department_id, department_name, parent_department_id
IZ odjela
WHERE parent_department_id JE NULL
UNIJA SVE
SELECT d.department_id, d.department_name, d.parent_department_id
IZ odjela d
PRIDRUŽITE SE Hijerarhiji odjela dh ON d.parent_department_id = dh.department_id
)
IZABERI *
FROM Hijerarhija odjela;
U ovom slučaju, rekurzivni CTE počinje s odjelima koji imaju NULL 'parent_department_id' (korijenski odjeli) i rekurzivno dohvaća podređene odjele.
Zaključak
U ovom smo vodiču naučili o najosnovnijim i najkorisnijim značajkama u SQL bazama podataka kao što su Common Table Expressions tako što smo razumjeli kako raditi s ključnom riječi WITH.