In SQL wird die WITH-Klausel auch als CTE bezeichnet. Es handelt sich um eine leistungsstarke Funktion, die es uns ermöglicht, temporäre Ergebnismengen innerhalb einer Abfrage zu erstellen. Eine Hauptaufgabe von CTEs besteht darin, komplexe Abfragen in kleinere und wiederverwendbare Unterabfragen zu vereinfachen. Dies trägt dazu bei, den Code auf lange Sicht besser lesbar und wartbar zu machen.
Nehmen Sie an diesem Tutorial teil und erkunden Sie die Funktionsweise der allgemeinen Tabellenausdrücke mithilfe der WITH-Klausel und der unterstützten Funktionalität.
Anforderungen:
Zu Demonstrationszwecken verwenden wir Folgendes:
- MySQL-Version 8.0 und höher
- Sakila-Beispieldatenbank
Wenn die gegebenen Anforderungen erfüllt sind, können wir mehr über CTEs und die WITH-Klausel erfahren.
SQL WITH-Klausel
Mit der WITH-Klausel können wir eine oder mehrere temporäre Ergebnismengen definieren, die als allgemeine Tabellenausdrücke bezeichnet werden.
Wir können die resultierenden CTEs in der Hauptabfrage wie jede andere Tabelle oder Ergebnismenge referenzieren. Dies spielt eine entscheidende Rolle bei der Erstellung modularer SQL-Abfragen.
Obwohl die Syntax von CTE je nach Ihren Anforderungen leicht variieren kann, zeigt das Folgende die grundlegende Syntax von CTE in SQL:
WITH cte_name (column1, columns2, ...) AS (
– CTE-Abfrage
WÄHLEN ...
AUS ...
WO ...
)
-- Hauptabfrage
WÄHLEN ...
AUS ...
JOIN cte_name ON ...
WO ...
Wir beginnen mit dem Schlüsselwort WITH, das der SQL-Datenbank mitteilt, dass wir CTE erstellen und verwenden möchten.
Als Nächstes geben wir den Namen für den CTE an, der es uns ermöglicht, ihn in anderen Abfragen zu referenzieren.
Wir geben auch eine optionale Liste von Spaltennamen an, wenn der CTE die Spaltenaliase enthält.
Als Nächstes definieren wir die CTE-Abfrage. Darin sind in Klammern alle Aufgaben bzw. Daten enthalten, die der CTE ausführt.
Zuletzt geben wir die Hauptabfrage an, die auf den CTE verweist.
Beispielverwendung:
Eine der besten Möglichkeiten, die Verwendung und Arbeit mit CTEs zu verstehen, besteht darin, sich ein praktisches Beispiel anzusehen.
Nehmen Sie zum Beispiel die Sakila-Beispieldatenbank. Angenommen, wir möchten die Top-10-Kunden mit der höchsten Anzahl an Anmietungen finden.
Schauen Sie sich den unten gezeigten CTE an.
Verwenden der SQL WITH-Klausel, um die zehn Kunden mit den höchsten Mietzahlen zu finden:
MIT CustomerRentals AS (SELECT c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS Rental_count
VON Kunde c
JOIN Rental r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
)
WÄHLEN *
VON CustomerRentals
ORDER BY Rental_count DESC
LIMIT 10;
Im gegebenen Beispiel definieren wir zunächst einen neuen CTE mit dem Schlüsselwort WITH, gefolgt von dem Namen, den wir dem CTE zuweisen möchten. In diesem Fall nennen wir es „CustomerRentals“.
Innerhalb des CTE-Körpers berechnen wir die Mietanzahl für jeden Kunden, indem wir die Kunden- und Miettabellen zusammenführen.
Schließlich wählen wir in der Hauptabfrage alle Spalten aus dem CTE aus, ordnen die Ergebnisse basierend auf der Mietanzahl (absteigende Reihenfolge) und beschränken die Ausgabe nur auf die obersten 10 Zeilen.
Dadurch können wir die Kunden mit der höchsten Anzahl an Anmietungen abrufen, wie in der folgenden Ausgabe gezeigt:
Rekursive CTEs
In einigen anderen Fällen haben Sie es möglicherweise mit hierarchischen Datenstrukturen zu tun. Hier kommen die rekursiven CTEs ins Spiel.
Nehmen wir als Beispiel einen Fall, in dem wir durch die hierarchische Organisation navigieren oder eine baumartige Struktur darstellen möchten. Wir können das Schlüsselwort WITH RECURSIVE verwenden, um einen rekursiven CTE zu erstellen.
Da es in der Sakila-Datenbank keine hierarchischen Daten gibt, die wir zur Demonstration eines rekursiven CTE verwenden können, erstellen wir ein einfaches Beispiel.
Abteilung TABELLE ERSTELLEN (Department_id INT PRIMARY KEY AUTO_INCREMENT,
Abteilungsname VARCHAR(255) NICHT NULL,
parent_department_id INT,
AUSLÄNDISCHER SCHLÜSSEL (parent_department_id) REFERENZEN Abteilung(department_id)
);
INSERT INTO Abteilung (Abteilungsname, übergeordnete_Abteilungs-ID)
WERTE
(„Unternehmen“, NULL),
(„Finanzen“, 1),
('HR', 1),
(„Buchhaltung“, 2),
„Rekrutierung“, 3),
(„Gehaltsabrechnung“, 4);
In diesem Fall haben wir eine Beispieltabelle „Abteilung“ mit einigen Zufallsdaten. Um die hierarchische Struktur der Abteilungen zu finden, können wir einen rekursiven CTE wie folgt verwenden:
WITH RECURSIVE DepartmentHierarchy AS (SELECT abteilungs-id, abteilungsname, übergeordnete abteilungs-id
VON der Abteilung
WO parent_department_id NULL IST
UNION ALLE
SELECT d.department_id, d.department_name, d.parent_department_id
VON Abteilung d
JOIN DepartmentHierarchy dh ON d.parent_department_id = dh.department_id
)
WÄHLEN *
VON AbteilungHierarchie;
In diesem Fall beginnt der rekursive CTE mit Abteilungen mit einer NULL-„parent_department_id“ (Stammabteilungen) und ruft rekursiv die untergeordneten Abteilungen ab.
Abschluss
In diesem Tutorial haben wir die grundlegendsten und nützlichsten Funktionen in SQL-Datenbanken wie die allgemeinen Tabellenausdrücke kennengelernt, indem wir verstanden haben, wie man mit dem Schlüsselwort WITH arbeitet.