Az SQL-ben a WITH záradék CTE néven is ismert. Ez egy hatékony funkció, amely lehetővé teszi ideiglenes eredményhalmazok létrehozását egy lekérdezésben. A CTE-k egyik fő szerepe az, hogy az összetett lekérdezéseket kisebb és újrafelhasználható segédlekérdezésekké egyszerűsíti. Ez segít abban, hogy a kód olvashatóbbá és hosszú távon karbantarthatóbbá váljon.
Csatlakozzon hozzánk ebben az oktatóanyagban, miközben a WITH záradék és a támogatott funkciók használatával fedezzük fel a Common Table Expressions működését.
Követelmények:
Demonstrációs célokra a következőket fogjuk használni:
- MySQL 8.0 és újabb verzió
- Sakila minta adatbázis
Ha a megadott követelmények teljesülnek, folytathatjuk a CTE-k és a WITH záradék további megismerését.
SQL záradékkal
A WITH záradék lehetővé teszi egy vagy több ideiglenes eredményhalmaz meghatározását, amelyeket Common Table Expressions néven ismerünk.
A kapott CTE-kre hivatkozhatunk a fő lekérdezésben, mint bármely más táblázatban vagy eredményhalmazban. Ez döntő szerepet játszik a moduláris SQL-lekérdezések létrehozásában.
Bár a CTE szintaxisa kissé eltérhet az Ön igényeitől függően, az alábbiakban látható a CTE alapvető szintaxisa SQL-ben:
WITH cte_name (oszlop1, oszlop2, ...) AS (
-- CTE lekérdezés
KIVÁLASZTÁS...
TÓL TŐL ...
AHOL ...
)
-- Fő lekérdezés
KIVÁLASZTÁS...
TÓL TŐL ...
CSATLAKOZÁS A cte_name BE...
AHOL ...
A WITH kulcsszóval kezdjük, amely közli az SQL adatbázissal, hogy CTE-t szeretnénk létrehozni és használni.
Ezután megadjuk a CTE nevét, amely lehetővé teszi, hogy más lekérdezésekben hivatkozhassunk rá.
Az oszlopnevek opcionális listáját is megadjuk, ha a CTE tartalmazza az oszlopálneveket.
Ezután folytatjuk a CTE lekérdezés meghatározását. Ez zárójelben tartalmazza a CTE által elvégzett összes feladatot vagy adatot.
Végül megadjuk a fő lekérdezést, amely a CTE-re hivatkozik.
Használati példa:
Az egyik legjobb módja annak, hogy megértsük, hogyan kell a CTE-ket használni és dolgozni, ha egy gyakorlati példát nézünk.
Vegyük például a Sakila mintaadatbázist. Tételezzük fel, hogy meg akarjuk találni a 10 legnagyobb bérlettel rendelkező ügyfelet.
Vessen egy pillantást a következő bemutatott CTE-re.
Az SQL WITH záradék használata a 10 legnagyobb bérleti díjjal rendelkező ügyfél megtalálásához:
A CustomerRentals AS-vel (SELECT c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS bérleti_szám
vevőtől c
JOIN rental r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
)
KIVÁLASZTÁS *
A Customer Rentals-tól
RENDELÉS ALAPJÁN bérleti_szám DESC
LIMIT 10;
Az adott példában egy új CTE definiálásával kezdjük a WITH kulcsszóval, majd a CTE-hez rendelni kívánt névvel. Ebben az esetben „CustomerRentals”-nak hívjuk.
A CTE törzsön belül az ügyfél és bérleti díj táblázathoz való csatlakozással kiszámítjuk az egyes ügyfelek bérleti díját.
Végül a fő lekérdezésben kiválasztjuk az összes oszlopot a CTE-ből, az eredményeket a bérleti díj alapján rendezzük (csökkenő sorrendben), és a kimenetet csak a felső 10 sorra korlátozzuk.
Ez lehetővé teszi számunkra, hogy a legtöbb bérlettel rendelkező ügyfeleket lekérjük, amint az a következő kimeneten látható:
Rekurzív CTE-k
Más esetekben előfordulhat, hogy hierarchikus adatstruktúrákkal kell foglalkoznia. Itt jönnek képbe a rekurzív CTE-k.
Vegyünk például egy esetet, amikor a hierarchikus szervezetben szeretnénk eligazodni, vagy egy faszerű struktúrát szeretnénk ábrázolni. A WITH RECURSIVE kulcsszóval rekurzív CTE-t hozhatunk létre.
Mivel a Sakila adatbázisban nincs olyan hierarchikus adat, amelyet felhasználhatnánk egy rekurzív CTE bemutatására, állítsunk fel egy alappéldát.
CREATE TABLE részleg (Department_id INT PRIMARY KEY AUTO_INCREMENT,
osztály_neve VARCHAR(255) NOT NULL,
parent_department_id INT,
IDEGEN KULCS (szülő_részleg_azonosítója) REFERENCIÁK osztály(részleg_azonosítója)
);
INSERT INTO osztály (részleg_neve, szülő_osztály_azonosítója)
ÉRTÉKEK
('Vállalati', NULL),
('Pénzügy', 1),
('HR', 1),
('Számvitel', 2),
„Toborzás”, 3),
(„Bérjegyzék”, 4);
Ebben az esetben van egy minta „részleg” táblázatunk néhány véletlenszerű adattal. Az osztályok hierarchikus felépítésének meghatározásához rekurzív CTE-t használhatunk a következőképpen:
REKURSÍV DepartmentHierarchy AS-vel (SELECT osztály_azonosító, osztály_neve, szülő_osztály_azonosítója
osztálytól
WHERE szülő_részleg_azonosítója NULL
UNION ALL
KIVÁLASZTÁS d.részleg_azonosító, d.részleg_neve, d.szülő_osztály_azonosítója
Osztálytól d
CSATLAKOZÁS a részleghierarchiához dh ON d.parent_department_id = dh.department_id
)
KIVÁLASZTÁS *
FROM OsztályHierarchia;
Ebben az esetben a rekurzív CTE NULL „szülőrészleg_azonosítóval” rendelkező részlegekkel kezdődik (gyökér osztályok), és rekurzívan lekéri az alárendelt részlegeket.
Következtetés
Ebben az oktatóanyagban megismerkedtünk az SQL-adatbázisok legalapvetőbb és leghasznosabb funkcióival, mint például a Common Table Expressions, mivel megértettük, hogyan kell dolgozni a WITH kulcsszóval.