Können Sie mehrere WITH-Anweisungen in SQL verwenden?

Ein umfassender Leitfaden für mehrere WITH-Anweisungen in SQL, perfekt für Anfänger und Experten gleichermaßen.

Mit der SQL-Klausel WITH können Sie einen CTE (common table expression) definieren. Ein CTE ist wie eine Tabelle, die während der Abfrageausführung gefüllt wird. Sie können mehrere WITH Anweisungen in einer SQL-Abfrage verwenden, um mehrere CTEs zu definieren. In diesem Artikel wird erklärt, wie Sie mehrere CTEs in einer einzigen Abfrage definieren können. Wir werden auch zeigen, wie dieser Ansatz die Ausdruckskraft einer SQL-Abfrage auf die nächste Stufe hebt.

Können Sie mehrere WITH-Anweisungen in SQL verwenden?

Ja! Nachfolgend sehen Sie eine einfache Abfrage mit zwei WITH-Klauseln:

WITH wine_exporting_country AS (
  SELECT country
  FROM product
  WHERE product_name = ‘wine’
  AND qty_exported > 0
),
car_exporting_country AS (
  SELECT country
  FROM product
  WHERE product_name = ‘car’
  AND qty_exported > 0
)
SELECT country
FROM wine_exporting_country 
INTERSECT car_exporting_country

In der obigen Abfrage gibt es zwei CTEs. Die erste (in rot) heißt wine_exporting_country und die zweite (in blau) ist car_exporting_country. Die Hauptabfrage (in schwarz) referenziert beide WITH Anweisungen als reguläre Tabellen mit INTERSECT. Die Schnittmenge der beiden Tabellen gibt nur die Länder zurück, die in beiden CTE-Ergebnissen enthalten sind.

Beachten Sie, dass Sie das Schlüsselwort WITH nur ein einziges Mal sehen können. Nach der WITH -Klausel müssen Sie den Namen des CTE angeben, dann das Schlüsselwort AS und schließlich in Klammern die SELECT -Abfrage, um den CTE zu definieren.

Wenn Sie lernen möchten, wie man mehrere WITH-Anweisungen in einer Abfrage verwendet, empfehle ich Ihnen unseren interaktiven Rekursive Abfragen Kurs. Darin lernen Sie die Details der WITH Klauselsyntax in über 100 praktischen Übungen kennen. Er behandelt die grundlegende Syntax, mehrere WITH Anweisungen und rekursive Abfragen - die komplexeste Verwendung der WITH Syntax.

Die WITH-Klausel in SQL

Lassen Sie uns zunächst erklären, wie die WITH Klausel funktioniert. Sie erstellt eine Art virtuelle Tabelle (die CTE), die während der Ausführung einer einzigen SQL-Anweisung erstellt und gefüllt wird; nach der SQL-Ausführung wird die CTE-"Tabelle" automatisch entfernt. Für ein besseres Verständnis der WITH Klausel empfehle ich den Artikel Was ist eine CTE?

In diesem Artikel verwenden wir eine Datenbanktabelle mit dem Namen product, in der die Länder und die beliebtesten Produkte, die sie herstellen (z. B. Olivenöl, Wein, Autos), erfasst werden. Sie erfasst die von jedem Land produzierte, importierte und exportierte Menge. Die Tabelle enthält auch den Preis des Produkts und die Einwohnerzahl des Landes.

Sehen wir uns ein einfaches Beispiel für eine Abfrage an, die eine WITH -Klausel verwendet, um ein CTE zu definieren. Angenommen, wir möchten eine Liste der Länder erhalten, die mehr Olivenöl exportieren als sie importieren. Die Ergebnisse sollen nach der von den einzelnen Ländern produzierten Menge geordnet werden. Hier ist die Abfrage:

WITH olive_oil_exporter AS (
  SELECT 
    country, 
    qty_produced
  FROM product
  WHERE product_name = ‘olive oil’
  AND qty_exported > qty_imported
)
SELECT country
FROM olive_oil_exporter
ORDER BY qty_produced DESC;

In dieser Abfrage haben wir eine CTE mit dem Namen olive_oil_exporter, die wie eine Tabelle mit zwei Spalten ist: country und qty_produced. Die Zeilen im CTE enthalten nur Länder, die mehr Olivenöl exportieren als sie importieren. Danach verwenden wir eine reguläre SELECT, um die CTE olive_oil_exporter nach den Ländernamen abzufragen, die wir absteigend nach der Menge des in diesem Land produzierten Olivenöls ordnen.

Verwendung mehrerer WITH-Anweisungen in SQL

Nehmen wir an, wir wollen die Länder in Länder, die Lebensmittel produzieren, und in Länder, die Technologie produzieren, unterteilen. Um ein Nahrungsmittelproduzent zu sein, muss ein Land mehr als 100 Millionen Dollar an Nahrungsmitteln exportieren. Um ein Hochtechnologieproduzent zu sein, muss ein Land mehr als 1000 Dollar an Technologieprodukten pro Einwohner produzieren.

Wir möchten einen Bericht mit den Namen aller Länder und zwei Spalten namens is_a_food_producer und is_a_hightech_producer erstellen. Die SQL-Abfrage für diesen Bericht lautet:

WITH food_producer AS (
  SELECT country
  FROM products
  WHERE product_family = ‘food’
  GROUP BY country
  HAVING SUM( qty_exported * unit_price) > 100000000
)
hightech_producer AS (
  SELECT country
  FROM products
  WHERE product_family = ‘technology’
  GROUP BY country
  HAVING SUM( qty_produced / country_population) > 1000
)
SELECT DISTINCT 
  p.country,
  CASE 
     WHEN fp.country IS NULL THEN ‘No’ 
     ELSE ‘Yes’ END AS is_a_food_produced,
  CASE 
    WHEN htp.country IS NULL THEN ‘No’ 
    ELSE ‘Yes’ END AS is_a_hightech_produced,
FROM products p
LEFT JOIN food_producer fp 
ON fp.country = p.country
LEFT JOIN hightech_producer htp 
ON htp.country = p.country

In der obigen Abfrage sind drei separate Abfragen zu erkennen. Die ersten beiden Abfragen verwenden die Klausel WITH, um zwei CTE-Tabellen zu definieren: food_producer und hightech_producer. Die dritte Abfrage ist die Hauptabfrage, die die beiden zuvor erstellten CTE-Tabellen verbraucht.

Nach der Klausel WITH sehen Sie den Namen der CTE-Tabelle (food_producer), dann die Unterklausel AS und schließlich (in Klammern) die Abfrage für diese CTE. Für den zweiten CTE ist die Klausel WITH nicht erforderlich; setzen Sie einfach ein Komma, und wiederholen Sie dann die gleiche Syntax, beginnend mit dem CTE-Namen.

Die beiden CTEs food_producer und hightech_producer haben nur eine Spalte: country. In der Hauptabfrage werden die Namen aller Länder aus der Tabelle productentnommen, dann wird eine LEFT JOIN gegen jede der CTEs durchgeführt. Wenn die LEFT JOIN keine übereinstimmende Zeile enthält, bedeutet dies, dass die Antwort für dieses Land "Nein" lautet; wenn es eine übereinstimmende Zeile gibt, lautet der Wert für dieses Land "Ja".

Bevor ich zum nächsten Abschnitt übergehe, möchte ich Ihnen den Artikel Wie man mehrere CTEs in SQL schreibt empfehlen. Darin finden Sie viele Beispiele und Erklärungen zu CTEs.

Verwendung von mehreren und verschachtelten WITH-Anweisungen in SQL

In manchen Fällen benötigen wir ein zweites CTE, das auf dem ersten CTE basiert: ein verschachteltes CTE. Mit anderen Worten, die Abfrage zur Definition des zweiten CTE muss einen Verweis auf den ersten CTE enthalten. Sehen wir uns ein Beispiel an.

Wir möchten den Gesamtdollarbetrag der von den Ländern exportierten Lebensmittelprodukte ermitteln. Im selben Bericht möchten wir den prozentualen Anteil dieses Betrags an den Gesamtexporten dieses Landes für alle Arten von Produkten anzeigen. Die Abfrage lautet:

WITH country_export_by_product AS (
  SELECT country, 
  product_family, 
  SUM(qty_exported * unit_price) AS total_exports
  FROM product
  GROUP BY country, product_family
),
country_export_total AS (
  SELECT country, 
  SUM(total_exports) AS total_exports_country
  FROM country_export_by_product 
  GROUP BY country
)
SELECT 
  cp.country, 
  product_family, 
  cp.total_exports_food ,
  ROUND((cp.total_exports_food / ct.total_exports_country) * 100, 2) 
     AS percentage_of_total_exports
FROM country_export_by_product cp
JOIN country_export_total ct 
ON ct.country = cp.country
ORDER BY country, product_family;

In dieser Abfrage haben wir eine CTE mit der Bezeichnung country_export_by_product erstellt, die die Spalten country, product_family und total_exports enthält (die den Gesamtbetrag der von diesem Land exportierten Produkte (in Dollar) darstellen). Beachten Sie, dass die Klausel GROUP BY die Spalten country und product_family verwendet.

Der nächste CTE heißt country_export_total und basiert auf dem vorherigen CTE country_export_by_product. Die Idee dieses CTE ist es, den Gesamtbetrag der Exporte jedes Landes auf der Grundlage des vorherigen CTE zu erhalten. Beachten Sie, dass wir im zweiten CTE eine GROUP BY Länderklausel verwenden. Der Grund für die zwei CTEs ist, dass wir verschiedene Ebenen der GROUP BY Klausel benötigen.

Die Hauptabfrage verweist auf beide CTEs und verbindet sie durch den Länderwert. Dann wird der Ausdruck

TRUNC((cp.total_exports_food / ct.total_exports_country) * 100, 2)

... verwendet, um den prozentualen Anteil der einzelnen Produkte an den Gesamtexporten des jeweiligen Landes zu berechnen.

Was die Syntax betrifft, so kann ein CTE in derselben Abfrage auf ein anderes CTE verweisen. Das haben wir in unserer Abfrage getan: Bei der Definition von CTE country_export_total haben wir auf das zuvor definierte CTE country_export_by_product verwiesen.

Beachten Sie, dass wir auf die Anweisung WITH verweisen können, die vor der aktuellen Anweisung WITH definiert wurde, aber nicht auf die danach. Sie können auf jede WITH-Anweisung mehrmals in einer anderen WITH-Anweisung oder in einer Hauptabfrage verweisen. In unserem Beispiel haben wir auf die erste definierte WITH (die country_export_by_product CTE) an zwei Stellen verwiesen: In der zweiten WITH (der country_export_total CTE) und in der Hauptabfrage.

Weitere Einschränkungen im Zusammenhang mit der Syntax der WITH-Klausel sind:

• Das Schlüsselwort WITH darf nur einmal verwendet werden, und zwar vor dem ersten CTE.
• Alle CTEs werden durch Kommas getrennt, aber es gibt kein Komma vor der Hauptabfrage. Dies entspricht dem Syntaxmuster:

            WITH cte_name1 AS (query1), 
           cte_name2 AS (query2) 
      main_query

Ich empfehle den Artikel SQL CTEs mit Beispielen erklärt für einige weitere Beispielabfragen WITH; sie zeigen, wie Sie die Organisation und Lesbarkeit Ihrer SQL-Abfragen mit CTEs verbessern können.

Verwendung der WITH-Klausel zum Erstellen Rekursive Abfragen

Sie verwenden die WITH Klausel in SQL, um rekursive Abfragen zu definieren. Mit rekursiven Abfragen können Sie hierarchische Strukturen abfragen (z. B. Organigramme, Bäume oder Diagramme). Mehr über die Abfrage von hierarchischen Strukturen erfahren Sie hier.

Rekursive Abfragen basieren auf der WITH Klausel. Um eine rekursive Abfrage zu erstellen, benötigen Sie nur eine WITH Klausel, aber die Abfrage innerhalb von WITH besteht aus zwei Teilen.

Rekursive Abfragen sind nützlich, wenn die Datenbanktabellen oder das Datenmodell eine implizite Art von Hierarchie aufweisen. Die vielleicht häufigste Beispieltabelle zur Erläuterung dieses Themas ist die typische Tabelle employee mit den Spalten employee_id und manager_employee_id.

Wenn wir einen Bericht erstellen wollen, der alle Mitarbeiter mit den Namen ihrer Vorgesetzten und der Hierarchiestufe des Mitarbeiters anzeigt, können wir die folgende rekursive Abfrage verwenden:

WITH RECURSIVE company_hierarchy AS (
  SELECT 
    employee_id, 
    firstname, 
    lastname, 
    manager_employee_id, 
    0 AS hierarchy_level
  FROM employees
  WHERE manager_employee_id IS NULL 
  UNION ALL 
  SELECT 
    e.employee_id, 
    e.firstname, 
    e.lastname, 
    e.manager_employee_id,
    hierarchy_level + 1
  FROM employees e, company_hierarchy ch
  WHERE e.manager_employee_id = ch.employee_id
)
SELECT 
  ch.firstname AS employee_first_name, 
  ch.lastname AS employee_last_name,
  e.firstname AS boss_first_name, 
  e.lastname AS boss_last_name,
  hierarchy_level
FROM company_hierarchy ch
LEFT JOIN employees e 
ON ch.manager_employee_id = e.employee_id
ORDER BY ch.hierarchy_level, ch.manager_employee_id;

In dieser Abfrage sehen wir die Klausel WITH RECURSIVE, mit der eine rekursive CTE namens company_hierarchy erstellt wird. Die CTE enthält alle Namen der Mitarbeiter mit den Namen ihrer Manager. Beachten Sie, dass die CTE zwei Anweisungen SELECT enthält, die durch UNION ALL verbunden sind. Die erste SELECT dient dazu, den ersten Mitarbeiter in der rekursiven Abfrage (CEO John Smith) zu ermitteln.

Die zweite SELECT der UNION ist eine Abfrage, die viele Male ausgeführt wird. Bei jeder Ausführung werden die Mitarbeiter der nächsten Hierarchiestufe zurückgegeben. Die erste Ausführung gibt beispielsweise alle Mitarbeiter zurück, die direkt an John Smith berichten.

Schließlich gibt es noch eine dritte SELECT Anweisung, die sich außerhalb der CTE befindet. Sie wählt die Namen der Mitarbeiter, die Namen ihrer Vorgesetzten und die Hierarchiestufe aus. Die Daten werden aus der CTE übernommen und mit der Tabelle employees. Wir verwenden eine LEFT JOIN, weil wir alle Daten aus der CTE haben wollen (einschließlich John Smith, der einen NULL-Wert in der Spalte manager_id hat). Die Ergebnisse werden in aufsteigender Reihenfolge angezeigt: zuerst nach der Hierarchieebene, dann nach der employee_id des Chefs. Unten sehen Sie das Ergebnis der Abfrage:

Wenn Sie mehr über rekursive Abfragen erfahren möchten, lesen Sie den Artikel Was ist eine rekursive CTE in SQL?

Lernen Sie weiter über mehrere WITH-Anweisungen in SQL

In diesem Artikel haben wir die Verwendung von mehreren WITH Anweisungen in einer einzigen SQL-Abfrage behandelt. Wir haben auch erwähnt, wie man die WITH Klausel in rekursiven Abfragen verwendet. Wenn Sie weiter über die WITH Klausel lernen möchten, empfehle ich Ihnen unseren Rekursive Abfragen Kurs, der eine ausgezeichnete Gelegenheit bietet, die anspruchsvollste Art von SQL-Abfragen zu üben.

Mehrere WITH Anweisungen sind am nützlichsten, wenn Sie komplexe SQL-Berichte schreiben. Wenn das bei Ihnen der Fall ist, empfehle ich Ihnen auch unser kostenloses Spickzettel für SQL zur Datenanalyse, das wir speziell für das Schreiben komplexer Abfragen zur Datenanalyse entwickelt haben.

Wenn Sie SQL auf fortgeschrittenem Niveau üben möchten, sollten Sie sich unseren Fortgeschrittenes SQL Practice Track ansehen. Er enthält über 200 Übungen, mit denen Sie fortgeschrittene SQL-Konzepte üben können. Jeden zweiten Monat veröffentlichen wir einen neuen SQL-Praxiskurs für Fortgeschrittene in unserem monatlichen SQL-Praxis -Track.

Sie können all diese Kurse und mehr auch in unserem Alle für immer Plan. Der Plan bietet Ihnen lebenslangen Zugang zu allen unseren SQL-Kursen auf verschiedenen Leistungsniveaus und in vier SQL-Dialekten. Melden Sie sich noch heute an!