Sa SQL, ang WITH clause ay kilala rin bilang CTE. Ito ay isang malakas na tampok na nagbibigay-daan sa amin na lumikha ng mga pansamantalang hanay ng resulta sa loob ng isang query. Ang isang pangunahing tungkulin ng mga CTE ay ang pagpapasimple sa mga kumplikadong query sa mas maliit at magagamit muli na mga subquery. Nakakatulong ito na gawing mas nababasa at mapanatili ang code sa katagalan.
Samahan kami sa tutorial na ito habang ginagalugad namin ang mga gumagana ng Common Table Expressions gamit ang WITH clause at sinusuportahang functionality.
Mga kinakailangan:
Para sa mga layunin ng pagpapakita, gagamitin namin ang sumusunod:
- MySQL bersyon 8.0 at mas mataas
- Sakila sample database
Sa ibinigay na mga kinakailangan, maaari tayong magpatuloy upang matuto nang higit pa tungkol sa mga CTE at sa sugnay na WITH.
SQL WITH Clause
Ang WITH clause ay nagbibigay-daan sa amin na tukuyin ang isa o higit pang pansamantalang set ng resulta na kilala bilang Common Table Expressions.
Maaari naming i-reference ang mga resultang CTE sa pangunahing query tulad ng iba pang talahanayan o set ng resulta. Ito ay gumaganap ng isang mahalagang papel sa paglikha ng modular SQL query.
Bagama't maaaring bahagyang mag-iba ang syntax ng CTE depende sa iyong mga kinakailangan, ipinapakita ng sumusunod ang pangunahing syntax ng CTE sa SQL:
MAY cte_name (column1, column2, ...) AS (
-- CTE Query
PUMILI...
MULA...
SAAN...
)
-- Pangunahing tanong
PUMILI...
MULA...
SUMALI sa cte_name SA ...
SAAN...
Nagsisimula kami sa WITH keyword na nagsasabi sa SQL database na gusto naming gawin at gamitin ang CTE.
Susunod, tinukoy namin ang pangalan para sa CTE na nagpapahintulot sa amin na i-reference ito sa iba pang mga query.
Tinutukoy din namin ang isang opsyonal na listahan ng mga pangalan ng column kung kasama sa CTE ang mga alias ng column.
Susunod, magpapatuloy kami upang tukuyin ang CTE query. Naglalaman ito ng lahat ng gawain o data na isinasagawa ng CTE na nakapaloob sa isang pares ng panaklong.
Panghuli, tinukoy namin ang pangunahing query na tumutukoy sa CTE.
Halimbawa ng Paggamit:
Ang isa sa mga pinakamahusay na paraan upang maunawaan kung paano gumamit at magtrabaho kasama ang mga CTE ay ang pagtingin sa isang praktikal na halimbawa.
Kunin halimbawa ang Sakila sample database. Ipagpalagay na nais naming mahanap ang nangungunang 10 mga customer na may pinakamataas na bilang ng mga rental.
Tingnan ang sumusunod na ipinapakitang CTE.
Gamit ang SQL WITH Clause para mahanap ang nangungunang 10 customer na may pinakamataas na bilang ng rental:
SA CustomerRentals AS (PUMILI c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) BILANG rental_count
MULA sa kostumer c
SUMALI sa rental r ON c.customer_id = r.customer_id
GROUP NG c.customer_id, c.first_name, c.last_name
)
SELECT *
MULA sa CustomerRentals
ORDER NG rental_count DESC
LIMIT 10;
Sa ibinigay na halimbawa, magsisimula kami sa pamamagitan ng pagtukoy ng bagong CTE gamit ang WITH keyword na sinusundan ng pangalan na gusto naming italaga sa CTE. Sa kasong ito, tinatawag namin itong 'CustomerRentals'.
Sa loob ng katawan ng CTE, kinakalkula namin ang bilang ng rental para sa bawat customer sa pamamagitan ng pagsali sa customer at talahanayan ng mga rental.
Panghuli, sa pangunahing query, pipiliin namin ang lahat ng column mula sa CTE, i-order ang mga resulta batay sa bilang ng rental (pababang pagkakasunud-sunod), at nililimitahan ang output sa nangungunang 10 row lang.
Nagbibigay-daan ito sa amin na makuha ang mga customer na may pinakamataas na bilang ng mga rental gaya ng ipinapakita sa sumusunod na output:
Mga recursive na CTE
Sa ilang iba pang mga kaso, maaari kang makitungo sa mga hierarchical na istruktura ng data. Dito pumapasok ang mga recursive CTE.
Isaalang-alang natin bilang halimbawa ang isang kaso kung saan gusto nating mag-navigate sa hierarchical na organisasyon o kumatawan sa isang istraktura na parang puno. Magagamit namin ang WITH RECURSIVE na keyword upang lumikha ng recursive CTE.
Dahil walang hierarchical data na magagamit natin sa database ng Sakila para magpakita ng recursive CTE, mag-set up tayo ng pangunahing halimbawa.
GUMAWA NG TABLE department (department_id INT PRIMARY KEY AUTO_INCREMENT,
department_name VARCHAR(255) NOT NULL,
parent_department_id INT,
FOREIGN KEY (parent_department_id) REFERENCES department(department_id)
);
INSERT INTO department (department_name, parent_department_id)
MGA HALAGA
('Corporate', NULL),
('Pananalapi', 1),
('HR', 1),
('Accounting', 2),
'Pagre-recruit', 3),
('Payroll', 4);
Sa kasong ito, mayroon kaming sample na talahanayan ng 'kagawaran' na may ilang random na data. Upang mahanap ang hierarchical na istraktura ng mga departamento, maaari kaming gumamit ng recursive CTE tulad ng sumusunod:
MAY RECURSIVE DepartmentHierarchy AS (PUMILI ng department_id, department_name, parent_department_id
MULA sa departamento
WHERE parent_department_id IS NULL
UNION LAHAT
PUMILI d.department_id, d.department_name, d.parent_department_id
MULA sa departamento d
SUMALI sa DepartmentHierarchy dh ON d.parent_department_id = dh.department_id
)
SELECT *
MULA sa DepartmentHierarchy;
Sa kasong ito, ang recursive CTE ay magsisimula sa mga departamentong mayroong NULL na “parent_department_id” (root department) at recursively na kinukuha ang mga child department.
Konklusyon
Sa tutorial na ito, natutunan namin ang tungkol sa mga pinakapangunahing at kapaki-pakinabang na feature sa mga database ng SQL tulad ng Common Table Expressions sa pamamagitan ng pag-unawa kung paano gamitin ang WITH keyword.