SQL:ssä WITH-lause tunnetaan myös nimellä CTE. Se on tehokas ominaisuus, jonka avulla voimme luoda tilapäisiä tulosjoukkoja kyselyn sisällä. Yksi CTE:n tärkeimmistä tehtävistä on yksinkertaistaa monimutkaiset kyselyt pienemmiksi ja uudelleenkäytettäviksi alikyselyiksi. Tämä auttaa tekemään koodista luettavamman ja ylläpidettävämmän pitkällä aikavälillä.
Liity tähän opetusohjelmaan, kun tutkimme yleisten taulukkolausekkeiden toimintaa WITH-lauseen ja tuettujen toimintojen avulla.
Vaatimukset:
Esittelytarkoituksiin käytämme seuraavia:
- MySQL-versio 8.0 ja uudemmat
- Sakila näytetietokanta
Kun annetut vaatimukset täyttyvät, voimme edetä saadaksemme lisätietoja CTE:istä ja WITH-lausekkeesta.
SQL lauseella
WITH-lauseen avulla voimme määrittää yhden tai useamman väliaikaisen tulosjoukon, jotka tunnetaan nimellä Common Table Expressions.
Voimme viitata tuloksena oleviin CTE:ihin pääkyselyssä kuten missä tahansa muussa taulukossa tai tulosjoukossa. Tällä on ratkaiseva rooli modulaaristen SQL-kyselyiden luomisessa.
Vaikka CTE:n syntaksi voi vaihdella hieman tarpeidesi mukaan, seuraava näyttää CTE:n perussyntaksin SQL:ssä:
WITH cte_name (sarake1, sarake2, ...) AS (
-- CTE-kysely
VALITSE...
LÄHETTÄ...
MISSÄ ...
)
-- Pääkysely
VALITSE...
LÄHETTÄ...
LIITY cte_name PÄÄLLÄ...
MISSÄ ...
Aloitamme WITH-avainsanalla, joka kertoo SQL-tietokannalle, että haluamme luoda ja käyttää CTE:tä.
Seuraavaksi määritämme CTE:n nimen, jonka avulla voimme viitata siihen muissa kyselyissä.
Määritämme myös valinnaisen luettelon sarakkeiden nimistä, jos CTE sisältää sarakkeiden aliakset.
Seuraavaksi siirrymme määrittelemään CTE-kyselyn. Tämä sisältää kaikki CTE:n suorittamat tehtävät tai tiedot suluissa.
Lopuksi määritämme pääkyselyn, joka viittaa CTE:hen.
Käyttöesimerkki:
Yksi parhaista tavoista ymmärtää CTE-laitteiden käyttöä ja työskentelyä niiden kanssa on katsoa käytännön esimerkkiä.
Otetaan esimerkiksi Sakilan näytetietokanta. Oletetaan, että haluamme löytää 10 parasta asiakasta, joilla on eniten vuokra-asuntoja.
Katso seuraavaa esitettyä CTE:tä.
SQL WITH -lausekkeen käyttäminen löytääksesi 10 parasta asiakasta, joilla on korkein vuokramäärä:
CustomerRentals AS:n kanssa (SELECT c.customer_id, c.first_name, c.sukunimi, COUNT(r.rental_id) AS vuokran_määrä
Asiakkaalta c
JOIN vuokraus r ON c.customer_id = r.customer_id
GROUP BY c.asiakastunnus, c.etunimi, c.sukunimi
)
VALITSE *
Customer Rentalsilta
TILAA vuokrausmäärän mukaan DESC
RAJA 10;
Annetussa esimerkissä aloitamme määrittelemällä uuden CTE:n käyttämällä WITH-avainsanaa ja sen jälkeen nimeä, jonka haluamme antaa CTE:lle. Tässä tapauksessa kutsumme sitä 'CustomerRentals'.
CTE-rungossa laskemme kunkin asiakkaan vuokramäärän liittymällä asiakas- ja vuokrataulukkoon.
Lopuksi pääkyselyssä valitsemme kaikki CTE:n sarakkeet, järjestämme tulokset vuokramäärän perusteella (laskevassa järjestyksessä) ja rajoitamme tulostuksen vain 10 ylimpään riviin.
Näin voimme hakea asiakkaat, joilla on eniten vuokra-asuntoja, kuten seuraavassa tulosteessa näkyy:
Rekursiiviset CTE:t
Joissakin muissa tapauksissa saatat olla tekemisissä hierarkkisten tietorakenteiden kanssa. Tässä rekursiiviset CTE:t tulevat peliin.
Otetaan esimerkiksi tapaus, jossa haluamme navigoida hierarkkisessa organisaatiossa tai edustaa puumaista rakennetta. Voimme käyttää WITH RECURSIVE -avainsanaa rekursiivisen CTE:n luomiseen.
Koska Sakila-tietokannassa ei ole hierarkkista dataa, jota voisimme käyttää rekursiivisen CTE:n havainnollistamiseen, laitetaan perusesimerkki.
CREATE TABLE -osasto (Department_id INT PRIMARY KEY AUTO_INCREMENT,
osaston_nimi VARCHAR(255) NOT NULL,
parent_department_id INT,
ULKOINEN AVAIN (vanhemman_osaston_tunnus) VIITTEET osasto(osastotunnus)
);
INSERT INTO osasto (osaston_nimi, vanhemman_osaston_tunnus)
ARVOT
('Yritys', NULL),
('Rahoitus', 1),
('HR', 1),
('Kirjanpito', 2),
'Rekrytointi', 3),
('Palkkalaskenta', 4);
Tässä tapauksessa meillä on esimerkki “osasto”-taulukosta, jossa on satunnaisia tietoja. Osastojen hierarkkisen rakenteen löytämiseksi voimme käyttää rekursiivista CTE:tä seuraavasti:
REKURSIIVINEN DepartmentHierarchy AS (VALITSE osastotunnus, osaston_nimi, vanhempi osastotunnus
osastolta
WHERE vanhempi_osastotunnus ON NULL
UNIONI KAIKKI
SELECT d.department_id, d.department_name, d.parent_department_id
osastolta d
LIITY osastohierarkiaan dh PÄÄLLÄ d.parent_department_id = dh.department_id
)
VALITSE *
FROM OsastoHierarkia;
Tässä tapauksessa rekursiivinen CTE alkaa osastoilla, joilla on NULL “parent_department_id” (juuriosastot) ja hakee rekursiivisesti alaosastot.
Johtopäätös
Tässä opetusohjelmassa opimme SQL-tietokantojen perustavanlaatuisimmista ja hyödyllisimmistä ominaisuuksista, kuten yleisistä taulukkolausekkeista, ymmärtämällä, kuinka WITH-avainsanaa käytetään.