SQL_Kurs

📐 SQL CTE · T-SQL Meisterklasse

Common Table Expressions (WITH-Klausel) für Microsoft SQL Server

Inhalt

📄 Basis CTE
🔢 Mehrere CTEs
🔄 Rekursion
🏷️ Fensterfunktionen
✏️ DML mit CTE

1. Basis CTE · Lesbarkeit & Wiederverwendung

📌 Zweck: Eine temporäre Ergebnismenge innerhalb einer Abfrage definieren, um komplexe Joins oder Aggregate zu vereinfachen.
Typisch für Berichte: Zuerst filtern / aggregieren, dann mit anderen Tabellen verknüpfen.


-- Beispiel: Umsatz pro Kategorie (angenommene Tabellenstruktur)
WITH SalesSummary AS (
  SELECT 
    p.CategoryID,
    SUM(od.Quantity * od.UnitPrice) AS TotalRevenue,
    COUNT(DISTINCT od.OrderID) AS NumberOfOrders
  FROM Sales.OrderDetails od
  INNER JOIN Production.Products p ON od.ProductID = p.ProductID
  WHERE od.OrderDate >= '2024-01-01'
  GROUP BY p.CategoryID
)
SELECT 
  c.CategoryName,
  ss.TotalRevenue,
  ss.NumberOfOrders,
  ROUND(ss.TotalRevenue / NULLIF(ss.NumberOfOrders, 0), 2) AS AvgOrderValue
FROM Production.Categories c
INNER JOIN SalesSummary ss ON c.CategoryID = ss.CategoryID
ORDER BY ss.TotalRevenue DESC;

💡 Erklärung: Die CTE SalesSummary fasst Verkaufszahlen pro Kategorie zusammen. Die Hauptabfrage nutzt diese virtuelle Tabelle für einen JOIN mit Kategorienamen und berechnet den durchschnittlichen Bestellwert. Vorteil: Logik getrennt, lesbarer als Unterabfragen.

2. Mehrere CTEs · Kaskadierte Logik

📌 Zweck: Mehrere benannte Ergebnislisten innerhalb derselben Abfrage definieren; spätere CTEs können auf frühere verweisen.


WITH ActiveCustomers AS (
  SELECT CustomerID, COUNT(OrderID) AS OrderCount
  FROM Sales.Orders
  WHERE OrderDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
  GROUP BY CustomerID
),
HighValue AS (
  SELECT CustomerID, OrderCount
  FROM ActiveCustomers
  WHERE OrderCount >= 5
),
AvgOrderAmount AS (
  SELECT 
    o.CustomerID,
    AVG(o.TotalDue) AS AvgOrderValue
  FROM Sales.SalesOrderHeader o
  WHERE o.CustomerID IN (SELECT CustomerID FROM HighValue)
    AND o.OrderDate >= '2024-01-01'
  GROUP BY o.CustomerID
)
SELECT 
  hv.CustomerID,
  hv.OrderCount,
  aoa.AvgOrderValue,
  c.FirstName + ' ' + c.LastName AS CustomerName
FROM HighValue hv
INNER JOIN Sales.Customer c ON hv.CustomerID = c.CustomerID
LEFT JOIN AvgOrderAmount aoa ON hv.CustomerID = aoa.CustomerID
ORDER BY hv.OrderCount DESC;

🔗 Erklärung: Drei CTEs bauen aufeinander auf – ActiveCustomers → HighValue → AvgOrderAmount. Beim Kompilieren werden sie wie Inline-Views behandelt. Ideale Struktur für mehrstufige Datenaufbereitung ohne reale Temp-Tabellen.

3. Rekursive CTE · Hierarchien & Graphen

📌 Zweck: Abfragen von hierarchischen Daten (Mitarbeiterstruktur, Kategoriebäume, Stücklisten). Benötigt Anchor und rekursiven Teil.


WITH OrgChart AS (
  -- Anchor: top-level Manager (kein Vorgesetzter)
  SELECT 
    EmployeeID,
    FirstName + ' ' + LastName AS EmployeeName,
    ManagerID,
    0 AS Level,
    CAST(FirstName + ' ' + LastName AS VARCHAR(500)) AS Path
  FROM HR.Employees
  WHERE ManagerID IS NULL

  UNION ALL

  -- Rekursiver Teil: alle untergeordneten Mitarbeiter
  SELECT 
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName,
    e.ManagerID,
    oc.Level + 1,
    CAST(oc.Path + ' → ' + e.FirstName + ' ' + e.LastName AS VARCHAR(500))
  FROM HR.Employees e
  INNER JOIN OrgChart oc ON e.ManagerID = oc.EmployeeID
)
SELECT 
  EmployeeID,
  EmployeeName,
  Level,
  Path,
  REPLICATE(' ', Level) + EmployeeName AS HierarchieDarstellung
FROM OrgChart
ORDER BY Path;

🧬 Erklärung: Die CTE ruft zuerst die Wurzel (CEO) ab. Im rekursiven Teil werden alle direkt oder indirekt unterstellten Mitarbeiter iterativ angehängt. Level zeigt die Tiefe. Wichtig: MAXRECURSION-Option bei tiefen Bäumen, standardmäßig 100 Iterationen (mit OPTION (MAXRECURSION 0) erweiterbar).

4. CTE mit Fensterfunktionen · ROW_NUMBER & Co.

📌 Zweck: CTEs sind ideal, um Fensterfunktionen zu verwenden und anschließend darauf zu filtern / aggregieren (z.B. Top-N pro Gruppe).


-- Beispiel: Pro Kategorie die 3 umsatzstärksten Produkte ermitteln
WITH ProductSales AS (
  SELECT 
    p.ProductID,
    p.ProductName,
    p.CategoryID,
    SUM(od.Quantity * od.UnitPrice) AS TotalSales,
    ROW_NUMBER() OVER (PARTITION BY p.CategoryID ORDER BY SUM(od.Quantity * od.UnitPrice) DESC) AS RankInCategory
  FROM Production.Products p
  INNER JOIN Sales.OrderDetails od ON p.ProductID = od.ProductID
  GROUP BY p.ProductID, p.ProductName, p.CategoryID
)
SELECT 
  CategoryID,
  ProductName,
  TotalSales,
  RankInCategory
FROM ProductSales
WHERE RankInCategory <= 3
ORDER BY CategoryID, RankInCategory;

-- Zusätzliches Beispiel mit LAG: monatliche Umsatzveränderung
WITH MonthlyRevenue AS (
  SELECT 
    YEAR(OrderDate) AS Year,
    MONTH(OrderDate) AS Month,
    SUM(TotalDue) AS Revenue,
    LAG(SUM(TotalDue), 1) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS PrevMonthRevenue
  FROM Sales.SalesOrderHeader
  GROUP BY YEAR(OrderDate), MONTH(OrderDate)
)
SELECT 
  Year,
  Month,
  Revenue,
  PrevMonthRevenue,
  FORMAT((Revenue - PrevMonthRevenue) / NULLIF(PrevMonthRevenue, 0), 'P2') AS GrowthRate
FROM MonthlyRevenue
ORDER BY Year, Month;

📊 Erklärung: Erste CTE verwendet ROW_NUMBER() OVER(PARTITION BY ...) um Produkte innerhalb jeder Kategorie zu bewerten. Danach können wir einfach auf RankInCategory filtern. Zweites Beispiel zeigt LAG() für Vorperiodenvergleich – erlaubt saubere Berechnung von Wachstumsraten ohne Self-Join.

5. CTE für DML · Aktualisieren/Löschen mit definierter Teilmenge

📌 Zweck: Eine CTE kann das Ziel einer UPDATE-, INSERT- oder DELETE-Anweisung sein, um gezielt bestimmte Datensätze zu ändern (z.B. doppelte Datensätze entfernen).


-- Beispiel 1: Duplikate in einer Tabelle löschen (basierend auf ROW_NUMBER)
WITH DuplicateCheck AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY Email, CustomerName ORDER BY CreatedDate DESC) AS rn
  FROM Sales.Customers
)
DELETE FROM DuplicateCheck WHERE rn > 1;

-- Beispiel 2: Preisaktualisierung für die teuersten 10% der Produkte
WITH TopProducts AS (
  SELECT TOP 10 PERCENT ProductID, UnitPrice
  FROM Production.Products
  ORDER BY UnitPrice DESC
)
UPDATE TopProducts 
SET UnitPrice = UnitPrice * 1.05; -- 5% Preiserhöhung für Luxusartikel

-- Beispiel 3: Daten archivieren und löschen (mit CTE umgesetzt)
WITH OldOrders AS (
  SELECT OrderID, OrderDate
  FROM Sales.Orders
  WHERE OrderDate < DATEADD(YEAR, -3, GETDATE())
)
DELETE FROM OldOrders;

⚡ Erklärung: In T-SQL können CTEs direkt als Zieltabelle für UPDATE, DELETE (und INSERT mit OUTPUT) verwendet werden. Die erste Abfrage löscht alle Datensätze mit rn>1 – entfernt Duplikate effizient. Die zweite erhöht den Preis der teuersten 10% Produkte. Wichtig: Die CTE muss aktualisierbar sein (keine Aggregation, kein DISTINCT, etc.).

🧠 CTE Grundsyntax (T-SQL)


WITH cte_name (column_list) AS (
  -- CTE query definition
  SELECT ...
)
-- Hauptabfrage (SELECT, INSERT, UPDATE, DELETE)
SELECT * FROM cte_name;

✅ T-SQL Besonderheiten: Rekursion mit UNION ALL, optional OPTION (MAXRECURSION n); CTEs können geschachtelt werden; nach einer CTE nur eine einzige Data Manipulation Language (DML)-Anweisung erlaubt. Performance: häufig optimiert der Optimizer CTEs wie Subqueries, keine automatische Temp-Tabelle – für große Datenmengen ggf. #temp-Tabelle in Erwägung ziehen.