📐 SQL CTE · T-SQL Meisterklasse
Common Table Expressions (WITH-Klausel) für Microsoft SQL Server
Lesbare, wiederverwendbare, rekursive Abfragen – alle Beispiele in reinem T-SQL (Azure SQL / SQL Server 2019+).
1. Basis CTE · Lesbarkeit & Wiederverwendung
NICHT REKURSIV
📌 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.
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
MEHRFACH CTE
📌 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
REKURSIV (UNION ALL)
📌 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.
ANALYTISCH
📌 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
UPDATE, DELETE
📌 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.