En SQL, la clàusula WITH també es coneix com a CTE. És una característica potent que ens permet crear conjunts de resultats temporals dins d'una consulta. Un paper important dels CTE és simplificar les consultes complexes en subconsultes més petites i reutilitzables. Això ajuda a fer que el codi sigui més llegible i fàcil de mantenir a llarg termini.
Uneix-te a nosaltres en aquest tutorial mentre explorem el funcionament de les expressions de taula comuns mitjançant la clàusula WITH i la funcionalitat admesa.
Requisits:
Amb finalitats de demostració, farem servir el següent:
- MySQL versió 8.0 i superior
- Base de dades de mostra de Sakila
Amb els requisits donats complerts, podem procedir a obtenir més informació sobre els CTE i la clàusula WITH.
Clàusula SQL WITH
La clàusula WITH ens permet definir un o més conjunts de resultats temporals que es coneixen com a expressions de taula comuns.
Podem fer referència als CTE resultants a la consulta principal com qualsevol altra taula o conjunt de resultats. Això té un paper crucial en la creació de consultes SQL modulars.
Tot i que la sintaxi de CTE pot variar lleugerament segons els vostres requisits, a continuació es mostra la sintaxi bàsica de CTE en SQL:
AMB nom_cte (columna1, columna2, ...) AS (
-- Consulta CTE
SELECCIONA...
DES de...
ON...
)
-- Consulta principal
SELECCIONA...
DES de...
UNEIX-TE a cte_name ON...
ON...
Comencem amb la paraula clau WITH que indica a la base de dades SQL que volem crear i utilitzar CTE.
A continuació, especifiquem el nom del CTE que ens permet fer-ne referència en altres consultes.
També especifiquem una llista opcional de noms de columnes si el CTE inclou els àlies de columna.
A continuació, procedim a definir la consulta CTE. Conté totes les tasques o dades que realitza el CTE entre parèntesis.
Finalment, especifiquem la consulta principal que fa referència al CTE.
Exemple d'ús:
Una de les millors maneres d'entendre com utilitzar i treballar amb CTE és mirar un exemple pràctic.
Prenguem per exemple la base de dades d'exemple de Sakila. Suposem que volem trobar els 10 millors clients amb el major nombre de lloguers.
Mireu el CTE mostrat a continuació.
Utilitzeu la clàusula SQL WITH per trobar els 10 millors clients amb el recompte de lloguer més alt:
AMB CustomerRentals AS (SELECT c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS lloguer_count
DEL client c
JOIN lloguer r ON c.customer_id = r.customer_id
GROUP BY c.id_client, c.nom_de_nom, c.cognom_de_client
)
SELECCIONA *
DE CustomerRentals
ORDENA PER Rental_count DESC
LÍMIT 10;
En l'exemple donat, comencem per definir un nou CTE utilitzant la paraula clau WITH seguida del nom que volem assignar al CTE. En aquest cas, l'anomenem 'CustomerRentals'.
Dins del cos del CTE, calculem el recompte de lloguers per a cada client unint-nos a la taula de clients i lloguers.
Finalment, a la consulta principal, seleccionem totes les columnes del CTE, ordenem els resultats en funció del recompte de lloguers (ordre descendent) i limitem la sortida només a les 10 primeres files.
Això ens permet recollir els clients amb el nombre més alt de lloguers, tal com es mostra a la sortida següent:
CTE recursius
En alguns altres casos, és possible que estigueu tractant amb estructures de dades jeràrquiques. Aquí és on entren en joc els CTE recursius.
Prenguem per exemple un cas en què volem navegar per l'organització jeràrquica o representar una estructura en forma d'arbre. Podem utilitzar la paraula clau WITH RECURSIVE per crear un CTE recursiu.
Com que no hi ha dades jeràrquiques que puguem utilitzar a la base de dades Sakila per demostrar un CTE recursiu, configurem un exemple bàsic.
departament CREAR TAULA (Department_id INT PRIMARY KEY AUTO_INCREMENT,
nom_departament VARCHAR(255) NOT NULL,
parent_department_id INT,
FOREIGN KEY (identificador_departament_parent) REFERÈNCIES departament (id_departament)
);
INSERT INTO departament (nom_departament, identificador_departament_parent)
VALORS
('Corporativa', NULL),
('Finances', 1),
('HR', 1),
('Comptabilitat', 2),
'Reclutament', 3),
('Nòmina', 4);
En aquest cas, tenim una taula de 'departament' de mostra amb algunes dades aleatòries. Per trobar l'estructura jeràrquica dels departaments, podem utilitzar un CTE recursiu de la següent manera:
AMB DepartmentHierarchy RECURSIU AS (SELECT Department_id, Department_name, parent_department_id
DEL departament
ON parent_department_id ÉS NULL
UNIÓ TOTS
SELECT d.department_id, d.department_name, d.parent_department_id
DEL departament d
JOIN DepartmentHierarchy dh ON d.parent_department_id = dh.department_id
)
SELECCIONA *
DE DepartamentJerarquia;
En aquest cas, el CTE recursiu comença amb els departaments que tenen un 'parent_department_id' NULL (departaments arrel) i recupera recursivament els departaments secundaris.
Conclusió
En aquest tutorial, vam aprendre sobre les funcions més fonamentals i útils de les bases de dades SQL com les expressions de taula comuns entenent com treballar amb la paraula clau WITH.