Rekursive Abfragen mithilfe von allgemeinen Tabellenausdrücken
Ein allgemeiner Tabellenausdruck (CTE, Common Table Expression) hat den Vorteil, dass er auf sich selbst verweisen kann, wodurch ein rekursiver CTE erstellt wird. Ein rekursiver CTE ist ein Ausdruck, bei dem ein ursprünglicher CTE wiederholt ausgeführt wird, um so lange Teilmengen von Daten zurückzugeben, bis das vollständige Resultset abgerufen wurde.
Eine Abfrage wird als rekursive Abfrage bezeichnet, wenn sie auf einen rekursiven CTE verweist. Die Rückgabe hierarchischer Daten ist eine häufige Verwendung rekursiver Abfragen, z. B. Anzeigen von Mitarbeitern in einem Organisationsdiagramm oder von Daten in einem Stücklistenszenario, in dem ein übergeordnetes Produkt eine oder mehrere Komponenten aufweist und diese Komponenten wiederum möglicherweise Unterkomponenten enthalten oder Komponenten anderer übergeordneter Elemente sind.
Ein rekursiver CTE kann erheblich zur Vereinfachung des Codes beitragen, der zur Ausführung einer rekursiven Abfrage innerhalb einer SELECT-, INSERT-, UPDATE-, DELETE- oder CREATE VIEW-Anweisung benötigt wird. In früheren Versionen von SQL Server setzt eine rekursive Abfrage üblicherweise die Verwendung temporärer Tabellen, Cursorn und Logik voraus, um den Ablauf der rekursiven Schritte zu steuern. Weitere Informationen zu allgemeinen Tabellenausdrücken (CTEs) finden Sie unter Verwenden allgemeiner Tabellenausdrücke.
Struktur eines rekursiven CTE
Die Struktur des rekursiven CTE in Transact-SQL entspricht der von rekursiven Routinen in anderen Programmiersprachen. Obwohl eine rekursive Routine in anderen Sprachen einen Skalarwert zurückgibt, kann ein rekursiver CTE mehrere Zeilen zurückgeben.
Ein rekursiver CTE besteht aus drei Elementen:
Aufruf der Routine.
Der erste Aufruf des rekursiven CTE besteht aus einer oder mehreren CTE_query_definitions, die durch die Operatoren UNION ALL, UNION, EXCEPT oder INTERSECT miteinander verknüpft sind. Da diese Abfragedefinitionen das Basisresultset der CTE-Struktur bilden, werden sie als Ankerelemente bezeichnet.
CTE_query_definitions werden als Ankerelemente betrachtet, sofern sie nicht auf das CTE selbst verweisen. Alle Ankerelement-Abfragedefinitionen müssen vor der ersten rekursiven Elementdefinition positioniert werden, und ein UNION ALL-Operator muss verwendet werden, um das letzte Ankerelement mit dem ersten rekursiven Element zu verknüpfen.
Rekursiver Aufruf der Routine.
Der rekursive Aufruf schließt eine oder mehrere CTE_query_definitions ein, die durch UNION ALL-Operatoren verknüpft sind, welche auf das CTE selbst verweisen. Diese Abfragedefinitionen werden als rekursive Elemente bezeichnet.
Beendigungsprüfung.
Die Beendigungsprüfung ist implizit; die Rekursion wird angehalten, wenn aus dem vorherigen Aufruf keine Zeilen mehr zurückgegeben werden.
.gif "Hinweis") Hinweis |
|---|
Ein fehlerhaft zusammengestellter rekursiver CTE kann zu einer Endlosschleife führen. So wird z. B. eine Endlosschleife erzeugt, wenn die Abfragedefinition des rekursiven Elements dieselben Werte sowohl für die übergeordneten als auch für die untergeordneten zurückgibt. Beim Testen der Ergebnisse einer rekursiven Abfrage können Sie die Anzahl der für eine bestimmte Anweisung zulässigen Rekursionsstufen einschränken, indem Sie den MAXRECURSION-Hinweis und einen Wert zwischen 0 und 32.767 in der OPTION-Klausel der INSERT-, UPDATE-, DELETE- oder SELECT-Anweisung verwenden. Weitere Informationen finden Sie unter Abfragehinweise (Transact-SQL) und WITH common_table_expression (Transact-SQL). |
Pseudocode und Semantik
Die Struktur des rekursiven CTE muss mindestens ein Ankerelement und ein rekursives Element enthalten. Der folgende Pseudocode zeigt die Komponenten eines einfachen rekursiven CTE, der ein Ankerelement und ein rekursives Element enthält.
WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name
Die Semantik der rekursiven Ausführung ist folgendermaßen:
Aufteilen des CTE-Ausdrucks in Ankerelemente und rekursive Elemente.
Ausführen der Ankerelemente zum Erzeugen des ersten Aufrufs oder Basisresultsets (T0).
Ausführen der rekursiven Elemente mit Ti als Eingabe und Ti+1 als Ausgabe.
Wiederholen von Schritt 3, bis ein leeres Set zurückgegeben wird.
Rückgabe des Resultsets. Das ist ein UNION ALL von T0 bis Tn.
Beispiel:
Das folgende Beispiel zeigt die Semantik der rekursiven CTE-Struktur durch Rückgabe einer hierarchischen Mitarbeiterliste, beginnend mit dem hochrangigsten Mitarbeiter im Adventure Works Cycles-Unternehmen. Anleitungen hinsichtlich der Codeausführung begleiten das Beispiel.
-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30) NOT NULL,
LastName nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES
(1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16, N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);
USE AdventureWorks2008R2;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, DeptID, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing' OR Level = 0;
GO
Anleitungen zum Beispielcode
Der rekursive CTE DirectReports definiert ein Ankerelement und ein rekursives Element.
Das Ankerelement gibt das Basisresultset T0 zurück. Dies ist der hochrangigste Mitarbeiter im Unternehmen, d. h. ein Mitarbeiter, der nicht gegenüber einem Manager rechenschaftspflichtig ist.
Hier ist das vom Ankerelement zurückgegebene Resultset:
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ NULL 1 Chief Executive Officer 0Das rekursive Element gibt die direkt Unterstellten des Mitarbeiters aus dem Resultset des Ankerelements zurück. Dies wird durch eine Joinoperation zwischen der Employee-Tabelle und dem CTE DirectReports erreicht. Es ist dieser Verweis auf den CTE selbst, der den rekursiven Aufruf verursacht. Basierend auf dem Mitarbeiter im CTE DirectReports als Eingabe (Ti) gibt die Verknüpfung (MyEmployees.ManagerID = DirectReports.EmployeeID) als Ausgabe (Ti+1) zurück, also die Mitarbeiter, für die (Ti) der Manager ist. Deshalb gibt die erste Iteration des rekursiven Elements dieses Resultset zurück:
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ 1 273 Vice President of Sales 1Das rekursive Element wird wiederholt aktiviert. Die zweite Iteration des rekursiven Elements verwendet das einzeilige Resultset von Schritt 3 (EmployeeID273 enthaltend) als Eingangswert und gibt dieses Resultset zurück:
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ 273 16 Marketing Manager 2 273 274 North American Sales Manager 2 273 285 Pacific Sales Manager 2Die dritte Iteration des rekursiven Elements verwendet das Resultset von oben als Eingangswert und gibt dieses Resultset zurück:
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ 16 23 Marketing Specialist 3 274 275 Sales Representative 3 274 276 Sales Representative 3 285 286 Sales Representative 3Das durch Ausführen der Abfrage zurückgegebene endgültige Resultset ist das Ergebnis der UNION-Operation aller Resultsets, die von den Anker- und rekursiven Elementen erzeugt wurden.
Hier das vom Beispiel zurückgegebene vollständige Resultset:
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ NULL 1 Chief Executive Officer 0 1 273 Vice President of Sales 1 273 16 Marketing Manager 2 273 274 North American Sales Manager 2 273 285 Pacific Sales Manager 2 16 23 Marketing Specialist 3 274 275 Sales Representative 3 274 276 Sales Representative 3 285 286 Sales Representative 3