În SQL, clauza WITH este cunoscută și ca CTE. Este o caracteristică puternică care ne permite să creăm seturi temporare de rezultate într-o interogare. Un rol major al CTE-urilor este simplificarea interogărilor complexe în subinterogări mai mici și reutilizabile. Acest lucru ajută la ca codul să fie mai lizibil și mai ușor de întreținut pe termen lung.
Alăturați-vă nouă în acest tutorial în timp ce explorăm funcționarea expresiilor comune de tabel folosind clauza WITH și funcționalitatea acceptată.
Cerințe:
În scopuri demonstrative, vom folosi următoarele:
- MySQL versiunea 8.0 și mai sus
- Baza de date de mostre Sakila
Cu cerințele date îndeplinite, putem continua să aflăm mai multe despre CTE și clauza WITH.
Clauza SQL WITH
Clauza WITH ne permite să definim unul sau mai multe seturi de rezultate temporare care sunt cunoscute sub numele de expresii comune de tabel.
Putem face referire la CTE-urile rezultate în interogarea principală ca orice alt tabel sau set de rezultate. Acest lucru joacă un rol crucial în crearea de interogări SQL modulare.
Deși sintaxa CTE poate varia ușor în funcție de cerințele dvs., următoarele arată sintaxa de bază a CTE în SQL:
WITH cte_name (coloana1, coloana2, ...) AS (
-- Interogare CTE
SELECTAȚI ...
DIN...
UNDE ...
)
-- Interogare principală
SELECTAȚI ...
DIN...
ÎNSCRIEȚI-VĂ pe cte_name PE...
UNDE ...
Începem cu cuvântul cheie WITH care spune bazei de date SQL că dorim să creăm și să folosim CTE.
Apoi, specificăm numele pentru CTE, care ne permite să-l referim în alte interogări.
De asemenea, specificăm o listă opțională de nume de coloane dacă CTE include aliasurile de coloane.
În continuare, trecem la definirea interogării CTE. Acesta conține toate sarcinile sau datele pe care le efectuează CTE incluse într-o pereche de paranteze.
În cele din urmă, specificăm interogarea principală care face referire la CTE.
Exemplu de utilizare:
Una dintre cele mai bune moduri de a înțelege cum să utilizați și să lucrați cu CTE-urile este să priviți un exemplu practic.
Luați, de exemplu, baza de date eșantion Sakila. Să presupunem că dorim să găsim primii 10 clienți cu cel mai mare număr de închirieri.
Aruncă o privire la următorul CTE afișat.
Folosind clauza SQL WITH pentru a găsi primii 10 clienți cu cel mai mare număr de închirieri:
CU CustomerRentals AS (SELECTează c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS numărul_închirieri
DE LA client c
ÎNSCRIEȚI-VĂ închirierea r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
)
SELECTAȚI *
DE LA CustomerRentals
ORDER BY rental_count DESC
LIMITA 10;
În exemplul dat, începem prin a defini un nou CTE folosind cuvântul cheie WITH urmat de numele pe care dorim să-l atribuim CTE. În acest caz, îl numim „CustomerRentals”.
În interiorul corpului CTE, calculăm numărul de închiriere pentru fiecare client prin alăturarea tabelului client și închirieri.
În cele din urmă, în interogarea principală, selectăm toate coloanele din CTE, ordonăm rezultatele pe baza numărului de închiriere (ordine descrescătoare) și limităm rezultatul la doar primele 10 rânduri.
Acest lucru ne permite să luăm clienții cu cel mai mare număr de închirieri, așa cum se arată în următoarea ieșire:
CTE recursive
În alte cazuri, este posibil să aveți de-a face cu structuri de date ierarhice. Aici intră în joc CTE-urile recursive.
Să luăm, de exemplu, un caz în care dorim să navigăm în organizarea ierarhică sau să reprezentăm o structură arborescentă. Putem folosi cuvântul cheie WITH RECURSIVE pentru a crea un CTE recursiv.
Deoarece nu există date ierarhice pe care să le putem folosi în baza de date Sakila pentru a demonstra un CTE recursiv, să creăm un exemplu de bază.
departament CREATE TABLE (Department_id INT PRIMARY KEY AUTO_INCREMENT,
nume_departament VARCHAR(255) NOT NULL,
parent_department_id INT,
CHEIE STRĂINĂ (parent_department_id) REFERENȚE departament(department_id)
);
INSERT INTO departament (nume_departament, id-departament_parent)
VALORI
(„Corporație”, NULL),
(„Finanțe”, 1),
(„HR”, 1),
(„Contabilitate”, 2),
„Recrutare”, 3),
(„Statul de plată”, 4);
În acest caz, avem un exemplu de tabel „departament” cu câteva date aleatorii. Pentru a găsi structura ierarhică a departamentelor, putem folosi un CTE recursiv după cum urmează:
CU Departamentul Ierarhie recursiv AS (SELECT ID_departament, nume_departament, ID_departament_parent
DIN departament
WHERE parent_department_id ESTE NULL
UNIREA TOȚI
SELECTAȚI d.department_id, d.department_name, d.parent_department_id
DIN departamentul d
JOIN DepartmentHierarchy dh ON d.parent_department_id = dh.department_id
)
SELECTAȚI *
DIN Departamentul Ierarhie;
În acest caz, CTE recursiv începe cu departamentele care au un „parent_department_id” NULL (departamente rădăcină) și recuperează recursiv departamentele copil.
Concluzie
În acest tutorial, am învățat despre cele mai fundamentale și utile caracteristici din bazele de date SQL, cum ar fi Common Table Expressions, înțelegând cum să lucrăm cu cuvântul cheie WITH.