SQL klauzula WITH ir pazīstama arī kā CTE. Tā ir jaudīga funkcija, kas ļauj vaicājumā izveidot pagaidu rezultātu kopas. Viena no galvenajām CTE funkcijām ir sarežģīto vaicājumu vienkāršošana mazākos un atkārtoti lietojamos apakšvaicājumos. Tas palīdz padarīt kodu lasāmāku un apkopjamāku ilgtermiņā.
Pievienojieties mums šajā apmācībā, pētot parasto tabulu izteiksmju darbību, izmantojot klauzulu WITH un atbalstīto funkcionalitāti.
Prasības:
Demonstrācijas nolūkos mēs izmantosim:
- MySQL versija 8.0 un jaunāka
- Sakila paraugu datubāze
Ja norādītās prasības ir izpildītas, mēs varam turpināt, lai uzzinātu vairāk par CTE un WITH klauzulu.
SQL AR klauzulu
WITH klauzula ļauj mums definēt vienu vai vairākas pagaidu rezultātu kopas, kas ir pazīstamas kā kopējās tabulas izteiksmes.
Mēs varam atsaukties uz iegūtajiem CTE galvenajā vaicājumā tāpat kā uz jebkuru citu tabulu vai rezultātu kopu. Tam ir izšķiroša nozīme modulāru SQL vaicājumu izveidē.
Lai gan CTE sintakse var nedaudz atšķirties atkarībā no jūsu prasībām, tālāk ir parādīta CTE pamata sintakse SQL:
WITH cte_name (kolonna1, kolonna 2, ...) AS (
-- CTE vaicājums
IZVĒLĒTIES...
NO...
KUR...
)
-- Galvenais vaicājums
IZVĒLĒTIES...
NO...
PIEVIENOTIES cte_name IESL.
KUR...
Mēs sākam ar atslēgvārdu WITH, kas norāda SQL datubāzei, ka mēs vēlamies izveidot un izmantot CTE.
Tālāk mēs norādām CTE nosaukumu, kas ļauj mums uz to atsaukties citos vaicājumos.
Mēs arī norādām neobligātu kolonnu nosaukumu sarakstu, ja CTE ir iekļauti kolonnu aizstājvārdi.
Tālāk mēs turpinām definēt CTE vaicājumu. Tajā ir visi uzdevumi vai dati, ko CTE veic, ievietoti iekavās.
Visbeidzot, mēs norādām galveno vaicājumu, kas atsaucas uz CTE.
Lietošanas piemērs:
Viens no labākajiem veidiem, kā saprast, kā lietot un strādāt ar CTE, ir aplūkot praktisku piemēru.
Piemēram, Sakila paraugu datubāze. Pieņemsim, ka mēs vēlamies atrast 10 labākos klientus ar vislielāko nomas piedāvājumu skaitu.
Apskatiet tālāk redzamo CTE.
Izmantojot klauzulu SQL WITH, lai atrastu 10 labākos klientus ar vislielāko nomas maksu:
AR CustomerRentals AS (ATLASĪT c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS rental_count
NO klienta c
JOIN nomas r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
)
ATLASĪT*
NO Customer Rentals
PASŪTĪT PĒC rental_count DESC
IEROBEŽOTS 10;
Dotajā piemērā mēs sākam ar jauna CTE definēšanu, izmantojot atslēgvārdu WITH, kam seko nosaukums, ko vēlamies piešķirt CTE. Šajā gadījumā mēs to saucam par “CustomerRentals”.
CTE korpusā mēs aprēķinām nomas maksu katram klientam, pievienojoties klientu un nomas maksu tabulai.
Visbeidzot, galvenajā vaicājumā mēs atlasām visas kolonnas no CTE, sakārtojam rezultātus, pamatojoties uz nomas skaitu (dilstošā secībā), un ierobežojam izvadi līdz tikai pirmajām 10 rindām.
Tas ļauj mums iegūt klientus ar vislielāko nomas piedāvājumu skaitu, kā parādīts tālāk norādītajā izvadā:
Rekursīvie CTE
Dažos citos gadījumos jums var būt darīšana ar hierarhiskām datu struktūrām. Šeit parādās rekursīvie CTE.
Ņemsim, piemēram, gadījumu, kad mēs vēlamies orientēties hierarhiskā organizācijā vai attēlot kokam līdzīgu struktūru. Mēs varam izmantot atslēgvārdu WITH RECURSIVE, lai izveidotu rekursīvu CTE.
Tā kā Sakila datubāzē nav hierarhisku datu, ko mēs varētu izmantot, lai demonstrētu rekursīvu CTE, izveidosim pamata piemēru.
CREATE TABLE nodaļa (Department_id INT PRIMARY KEY AUTO_INCREMENT,
nodaļas_nosaukums VARCHAR(255) NOT NULL,
parent_department_id INT,
ĀRĒJĀ ATSLĒGA (parent_department_id) ATSAUKSMES nodaļa (department_id)
);
INSERT INTO nodaļa (nodaļas_nosaukums, vecāku_nodaļas_id)
VĒRTĪBAS
('Uzņēmums', NULL),
('Finanses', 1),
('HR', 1),
('Grāmatvedība', 2),
“Rekrutēšana”, 3),
('Algu saraksts', 4);
Šajā gadījumā mums ir “nodaļas” tabulas paraugs ar dažiem nejaušiem datiem. Lai atrastu nodaļu hierarhisko struktūru, mēs varam izmantot rekursīvo CTE šādi:
AR REKURSĪVO DepartmentHierarchy AS (ATLASĪT nodaļas_id, nodaļas_nosaukums, vecāku_nodaļas_id
NO nodaļas
KUR vecāku_nodaļas_id IR NULL
SAVIENĪBA VISU
ATLASĪT d.department_id, d.department_name, d.parent_department_id
NO nodaļas d
PIEVIENOTIES departamenta hierarhijai dh IESLĒGTS d.parent_department_id = dh.department_id
)
ATLASĪT*
NO nodaļasHierarhija;
Šajā gadījumā rekursīvais CTE sākas ar nodaļām, kurām ir NULL “parent_department_id” (saknes nodaļas), un rekursīvi izgūst pakārtotās nodaļas.
Secinājums
Šajā apmācībā mēs uzzinājām par svarīgākajām un noderīgākajām funkcijām SQL datu bāzēs, piemēram, Common Table Expressions, saprotot, kā strādāt ar atslēgvārdu WITH.