WITH
Definiert eine benannte Unterabfrage (Common Table Expression, CTE), die die Hauptabfrage wie eine Tabelle verwendet.
WITH [RECURSIVE] name_cte [(column[, ...])] AS (subquery) main_queryParameter
- RECURSIVESchlüsselwortoptional
- Erlaubt dem Tabellenausdruck, auf sich selbst zu verweisen
- name_cteName
- Name des Tabellenausdrucks
- columnNameoptional
- Spaltennamen des Tabellenausdrucks, standardmäßig aus subquery übernommen
- subqueryUnterabfrage
- Unterabfrage, deren Ergebnis unter dem Namen name_cte verfügbar ist
- main_queryUnterabfrage
- Hauptabfrage, die name_cte wie eine Tabelle verwendet
Rückgabewert
Temporäre benannte Ergebnismenge, die nur in dieser Abfrage verfügbar ist
Beispiele
Anzahl der Flüge pro Fluggesellschaft
WITH company_trips AS (
SELECT company,
COUNT(*) AS trips
FROM Trip
GROUP BY company
)
SELECT Company.name,
company_trips.trips
FROM company_trips
JOIN Company ON Company.id = company_trips.company
ORDER BY company_trips.trips DESC;Zahlen 1 bis 5 mit RECURSIVE
WITH RECURSIVE numbers (n) AS (
SELECT 1
UNION ALL
SELECT n + 1
FROM numbers
WHERE n < 5
)
SELECT n
FROM numbers;String wächst in der Rekursion
WITH RECURSIVE stars (n, line) AS (
SELECT 1,
CAST('*' AS CHAR(10))
UNION ALL
SELECT n + 1,
CONCAT(line, '*')
FROM stars
WHERE n < 5
)
SELECT n,
line
FROM stars;Details
Nach einem WITH kannst du mehrere Tabellenausdrücke durch Kommas getrennt angeben, und jeder kann auf die vorherigen verweisen.
Ein rekursiver Ausdruck besteht aus einer Startabfrage und einem rekursiven Teil, die mit UNION ALL oder UNION (das zusätzlich Wiederholungen entfernt) verbunden sind, und braucht eine Abbruchbedingung. Standardmäßig läuft der rekursive Teil höchstens 1000 Mal (Variable cte_max_recursion_depth), danach bricht die Abfrage mit einem Fehler ab.
Die Spaltentypen legt die Startabfrage fest. Wenn der rekursive Teil einen String verlängert, mach den Startwert vorher breiter: CAST('*' AS CHAR(10)), sonst gibt es den Fehler «Data too long».