SQL 11: Common Table Expressions (WITH CTE)

🧱 1. Was sind Common Table Expressions (CTEs)?

Eine CTE (Common Table Expression) definiert ein temporäres, benanntes Zwischenergebnis mit dem Schlüsselwort WITH. Sie ersetzt unübersichtlich tief verschachtelte Subqueries durch eine saubere, lineare Schritt-für-Schritt-Pipeline:

-- Modulare Umsatzberechnung mit WITH CTE
WITH kunden_umsatz AS (
    SELECT 
        kunde_id,
        COUNT(id) AS anzahl_orders,
        SUM(gesamtbetrag) AS umsatz_summe
    FROM bestellungen
    WHERE status = 'abgeschlossen'
    GROUP BY kunde_id
)
SELECT 
    k.name,
    ku.anzahl_orders,
    ku.umsatz_summe
FROM kunden k
JOIN kunden_umsatz ku ON k.id = ku.kunde_id
WHERE ku.umsatz_summe > 1000
ORDER BY ku.umsatz_summe DESC;

⛓️ 2. Gekettete (Chained) & Rekursive CTEs

Du kannst mehrere CTEs mit Kommas aneinanderhängen (Pipelines), wobei spätere CTEs auf frühere zugreifen dürfen:

WITH 
monats_daten AS (
    SELECT strftime('%Y-%m', datum) AS monat, SUM(betrag) AS umsatz FROM sales GROUP BY monat
),
benchmark AS (
    SELECT AVG(umsatz) AS avg_umsatz FROM monats_daten
)
SELECT m.monat, m.umsatz, b.avg_umsatz, (m.umsatz - b.avg_umsatz) AS diff
FROM monats_daten m, benchmark b;

WITH RECURSIVE organigramm AS (
    -- 1. ANKER-MEMBER (Root / CEO: vorgesetzter_id IS NULL)
    SELECT id, name, vorgesetzter_id, 1 AS ebene, name AS pfad
    FROM mitarbeiter WHERE vorgesetzter_id IS NULL
    
    UNION ALL
    
    -- 2. REKURSIONS-MEMBER (Kind-Knoten rekursiv joinen)
    SELECT m.id, m.name, m.vorgesetzter_id, o.ebene + 1, o.pfad || ' -> ' || m.name
    FROM mitarbeiter m
    JOIN organigramm o ON m.vorgesetzter_id = o.id
)
SELECT * FROM organigramm ORDER BY ebene, name;

🏭 3. Didaktische Analogie: Das modulare Fließband

📑 4. Cheat-Sheet: CTE-Syntax & Muster

🧠 Diagnostischer Selbst-Check

Überprüfe dein Verständnis der Kernkonzepte und typischen Denkfallen, bevor du den Code in der Web-IDE schreibst.

🎯 5. Aufgaben & Checkliste in aufgabe.sql

Öffne die Datei aufgabe.sql und implementiere die 4 Aufgaben: