V SQL je klauzule WITH známá také jako CTE. Je to výkonná funkce, která nám umožňuje vytvářet dočasné sady výsledků v rámci dotazu. Jednou z hlavních rolí CTE je zjednodušení složitých dotazů na menší a opakovaně použitelné poddotazy. To pomáhá, aby byl kód čitelnější a udržitelný z dlouhodobého hlediska.
Připojte se k nám v tomto kurzu, když prozkoumáme fungování společných tabulkových výrazů pomocí klauzule WITH a podporovaných funkcí.
Požadavky:
Pro demonstrační účely použijeme následující:
- MySQL verze 8.0 a vyšší
- Ukázková databáze Sakila
Po splnění daných požadavků můžeme přistoupit k tomu, abychom se dozvěděli více o CTE a klauzuli WITH.
SQL S klauzulí
Klauzule WITH nám umožňuje definovat jednu nebo více dočasných sad výsledků, které jsou známé jako Common Table Expressions.
Můžeme odkazovat na výsledné CTE v hlavním dotazu jako na jakoukoli jinou tabulku nebo sadu výsledků. To hraje zásadní roli při vytváření modulárních SQL dotazů.
Ačkoli se syntaxe CTE může mírně lišit v závislosti na vašich požadavcích, níže ukazuje základní syntaxi CTE v SQL:
WITH cte_name (sloupec1, sloupec2, ...) AS (
-- Dotaz CTE
VYBRAT...
Z ...
KDE...
)
-- Hlavní dotaz
VYBRAT...
Z ...
PŘIPOJTE SE k cte_name ON ...
KDE...
Začneme klíčovým slovem WITH, které říká SQL databázi, že chceme vytvořit a používat CTE.
Dále uvedeme název pro CTE, který nám umožní odkazovat na něj v jiných dotazech.
Uvádíme také volitelný seznam názvů sloupců, pokud CTE obsahuje aliasy sloupců.
Dále přistoupíme k definici dotazu CTE. To obsahuje všechny úkoly nebo data, která CTE provádí, uzavřená v páru závorek.
Nakonec specifikujeme hlavní dotaz, který odkazuje na CTE.
Příklad použití:
Jedním z nejlepších způsobů, jak pochopit, jak používat a pracovat s CTE, je podívat se na praktický příklad.
Vezměte si například vzorovou databázi Sakila. Předpokládejme, že chceme najít 10 nejlepších zákazníků s nejvyšším počtem pronájmů.
Podívejte se na níže zobrazený CTE.
Použití klauzule SQL WITH k nalezení 10 nejlepších zákazníků s nejvyšším počtem pronájmů:
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
PŘIPOJTE SE k pronájmu r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
)
VYBRAT *
OD CustomerRentals
OBJEDNEJTE PODLE počtu_nájmu DESC
LIMIT 10;
V uvedeném příkladu začneme definováním nového CTE pomocí klíčového slova WITH následovaného názvem, který chceme CTE přiřadit. V tomto případě to nazýváme „CustomerRentals“.
Uvnitř těla CTE vypočítáme počet pronájmů pro každého zákazníka připojením k tabulce zákazníků a pronájmů.
Nakonec v hlavním dotazu vybereme všechny sloupce z CTE, seřadíme výsledky na základě počtu pronájmů (sestupné pořadí) a omezíme výstup pouze na prvních 10 řádků.
To nám umožňuje získat zákazníky s nejvyšším počtem pronájmů, jak je uvedeno v následujícím výstupu:
Rekurzivní CTE
V některých jiných případech můžete mít co do činění s hierarchickými datovými strukturami. Zde vstupují do hry rekurzivní CTE.
Vezměme si například případ, kdy chceme procházet hierarchickou organizací nebo představovat stromovou strukturu. K vytvoření rekurzivního CTE můžeme použít klíčové slovo WITH RECURSIVE.
Protože v databázi Sakila neexistují žádná hierarchická data, která bychom mohli použít k demonstraci rekurzivního CTE, uveďme základní příklad.
oddělení CREATE TABLE (department_id INT PRIMÁRNÍ KLÍČ AUTO_INCREMENT,
název_oddělení VARCHAR(255) NOT NULL,
parent_department_id INT,
CIZÍ KLÍČ (id_parent_department_id) REFERENCE department(department_id)
);
INSERT INTO department (department_name, parent_department_id)
HODNOTY
('Firemní', NULL),
('Finance', 1),
('HR', 1),
('Účetnictví', 2),
'Nábor', 3),
('Výplatní listina', 4);
V tomto případě máme vzorovou tabulku „oddělení“ s některými náhodnými údaji. K nalezení hierarchické struktury oddělení můžeme použít rekurzivní CTE takto:
S REKURZIVNÍM oddělením Hierarchie AS (SELECT department_id, department_name, parent_department_id
Z oddělení
WHERE parent_department_id JE NULL
UNION VŠECHNY
SELECT d.department_id, d.department_name, d.parent_department_id
Z oddělení d
PŘIPOJTE SE k hierarchii oddělení dh ON d.parent_department_id = dh.department_id
)
VYBRAT *
FROM DepartmentHierarchy;
V tomto případě rekurzivní CTE začíná odděleními s NULL „parent_department_id“ (kořenová oddělení) a rekurzivně získává podřízená oddělení.
Závěr
V tomto tutoriálu jsme se dozvěděli o nejzákladnějších a nejužitečnějších funkcích v databázích SQL, jako jsou Common Table Expressions, tím, že jsme pochopili, jak pracovat s klíčovým slovem WITH.