V SQL je klauzula WITH známa aj ako CTE. Je to výkonná funkcia, ktorá nám umožňuje vytvárať dočasné sady výsledkov v rámci dotazu. Jednou z hlavných úloh CTE je zjednodušenie zložitých dopytov na menšie a opakovane použiteľné poddotazy. To pomáha, aby bol kód z dlhodobého hľadiska čitateľnejší a udržiavateľný.
Pripojte sa k nám v tomto návode, keď skúmame fungovanie bežných tabuľkových výrazov pomocou klauzuly WITH a podporovaných funkcií.
Požiadavky:
Na demonštračné účely použijeme nasledovné:
- MySQL verzie 8.0 a vyššej
- Vzorová databáza Sakila
Po splnení daných požiadaviek sa môžeme dozvedieť viac o CTE a klauzule WITH.
SQL S doložkou
Klauzula WITH nám umožňuje definovať jednu alebo viacero dočasných sád výsledkov, ktoré sú známe ako spoločné výrazy tabuliek.
Na výsledné CTE môžeme odkazovať v hlavnom dotaze ako na akúkoľvek inú tabuľku alebo množinu výsledkov. To hrá kľúčovú úlohu pri vytváraní modulárnych SQL dotazov.
Aj keď sa syntax CTE môže mierne líšiť v závislosti od vašich požiadaviek, nižšie uvádzame základnú syntax CTE v SQL:
WITH cte_name (stĺpec1, stĺpec2, ...) AS (
-- CTE dotaz
VYBRAŤ...
OD...
KDE ...
)
-- Hlavný dotaz
VYBRAŤ...
OD...
PRIPOJTE SA cte_name ON ...
KDE ...
Začneme kľúčovým slovom WITH, ktoré hovorí SQL databáze, že chceme vytvoriť a používať CTE.
Ďalej zadáme názov pre CTE, ktorý nám umožňuje odkazovať naň v iných dotazoch.
Špecifikujeme tiež voliteľný zoznam názvov stĺpcov, ak CTE obsahuje aliasy stĺpcov.
Ďalej pristúpime k definovaniu CTE dotazu. Obsahuje všetky úlohy alebo údaje, ktoré CTE vykonáva, uzavreté v zátvorkách.
Nakoniec špecifikujeme hlavný dotaz, ktorý odkazuje na CTE.
Príklad použitia:
Jedným z najlepších spôsobov, ako pochopiť, ako používať a pracovať s CTE, je pozrieť sa na praktický príklad.
Vezmite si napríklad vzorovú databázu Sakila. Predpokladajme, že chceme nájsť 10 najlepších zákazníkov s najvyšším počtom prenájmov.
Pozrite sa na nasledujúce zobrazené CTE.
Použitie klauzuly SQL WITH na nájdenie 10 najlepších zákazníkov s najvyšším počtom prenájmov:
WITH CustomerRentals AS (SELECT c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS rental_count
OD zákazníka c
PRIPOJTE sa k prenájmu r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
)
VYBRAŤ *
OD CustomerRentals
ORDER BY rental_count DESC
LIMIT 10;
V uvedenom príklade začneme definovaním nového CTE pomocou kľúčového slova WITH, za ktorým nasleduje názov, ktorý chceme CTE priradiť. V tomto prípade to nazývame „CustomerRentals“.
Vo vnútri tela CTE vypočítame počet prenájmov pre každého zákazníka tak, že sa pripojíme k tabuľke zákazníkov a prenájmov.
Nakoniec v hlavnom dotaze vyberieme všetky stĺpce z CTE, zoradíme výsledky na základe počtu prenájmov (zostupné poradie) a obmedzíme výstup len na prvých 10 riadkov.
To nám umožňuje získať zákazníkov s najvyšším počtom prenájmov, ako je uvedené v nasledujúcom výstupe:
Rekurzívne CTE
V niektorých iných prípadoch môžete mať do činenia s hierarchickými dátovými štruktúrami. Tu vstupujú do hry rekurzívne CTE.
Vezmime si napríklad prípad, keď sa chceme pohybovať v hierarchickej organizácii alebo reprezentovať stromovú štruktúru. Na vytvorenie rekurzívneho CTE môžeme použiť kľúčové slovo WITH RECURSIVE.
Keďže neexistujú žiadne hierarchické údaje, ktoré by sme mohli použiť v databáze Sakila na demonštráciu rekurzívneho CTE, nastavíme základný príklad.
oddelenie CREATE TABLE (department_id INT PRIMARY KEY AUTO_INCREMENT,
department_name VARCHAR(255) NOT NULL,
parent_department_id INT,
CUDZÍ KĽÚČ (id_rodičovského_oddelenia) REFERENCIE oddelenie(id_oddelenia)
);
INSERT INTO department (department_name, parent_department_id)
HODNOTY
('Firemné', NULL),
('Financie', 1),
('HR', 1),
('účtovníctvo', 2),
'Nábor', 3),
(„Mzdy“, 4);
V tomto prípade máme vzorovú tabuľku „oddelení“ s niektorými náhodnými údajmi. Na nájdenie hierarchickej štruktúry oddelení môžeme použiť rekurzívny CTE takto:
S REKURZÍVNYM oddelením Hierarchia AS (SELECT department_id, department_name, parent_department_id
Z oddelenia
WHERE parent_department_id JE NULL
UNION VŠETKO
SELECT d.department_id, d.department_name, d.parent_department_id
Z oddelenia d
PRIPOJTE SA k hierarchii oddelenia dh ON d.parent_department_id = dh.department_id
)
VYBRAŤ *
FROM DepartmentHierarchy;
V tomto prípade rekurzívne CTE začína oddeleniami, ktoré majú NULL „parent_department_id“ (koreňové oddelenia) a rekurzívne získava podradené oddelenia.
Záver
V tomto návode sme sa dozvedeli o najzákladnejších a najužitočnejších funkciách v databázach SQL, ako sú spoločné výrazy tabuliek tým, že sme pochopili, ako pracovať s kľúčovým slovom WITH.