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.
In SQL Server 2005 wird eine Abfrage als rekursive Abfrage bezeichnet, wenn sie auf einen rekursiven CTE verweisen. 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 einem 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 "ms186243.note(de-de,SQL.90).gif") 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 Abfragehinweis (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. Die Anweisung zur Ausführung des CTE schränkt das Resultset auf Mitarbeiter in der Research and Development Group ein. Anleitungen hinsichtlich der Codeausführung begleiten das Beispiel.
USE AdventureWorks;
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 HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID 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 HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0;
GO
Anleitungen zum Beispielcode
Der rekursive CTE
DirectReportsdefiniert 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 109 Chief Executive Officer 0Das rekursive Element gibt die direkt Unterstellten des Mitarbeiters aus dem Resultset des Ankerelements zurück. Dies wird durch eine Verknüpfungsoperation zwischen der
Employee-Tabelle und dem CTEDirectReportserreicht. Es ist dieser Verweis auf den CTE selbst, der den rekursiven Aufruf verursacht. Basierend auf dem Mitarbeiter im CTEDirectReportsals Eingabe (Ti) gibt die Verknüpfung(Employee.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 --------- ---------- --------------------------------------- ------ 109 12 Vice President of Engineering 1Das rekursive Element wird wiederholt aktiviert. Die zweite Iteration des rekursiven Elements verwendet das einzeilige Resultset von Schritt 3 (
EmployeeID``12enthaltend) als Eingangswert und gibt dieses Resultset zurück:ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 12 3 Engineering Manager 2Die dritte Iteration des rekursiven Elements verwendet das einzeilige Resultset von oben (
EmployeeID``3)enthaltend) als Eingangswert und gibt dieses Resultset zurück:ManagerID EmployeeID Title Level --------- ---------- --------------------------------------- ------ 3 4 Senior Tool Designer 3 3 9 Design Engineer 3 3 11 Design Engineer 3 3 158 Research and Development Manager 3 3 263 Senior Tool Designer 3 3 267 Senior Design Engineer 3 3 270 Design Engineer 3Die vierte Iteration des rekursiven Elements verwendet das vorherige Zeilenset für
EmployeeID-Werte4,9,11,158,263,267und270als Eingabewert.
Dieser Prozess wird wiederholt, bis das rekursive Element ein leeres Resultset zurückgibt.Das 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 109 Chief Executive Officer 0 109 12 Vice President of Engineering 1 12 3 Engineering Manager 2 3 4 Senior Tool Designer 3 3 9 Design Engineer 3 3 11 Design Engineer 3 3 158 Research and Development Manager 3 3 263 Senior Tool Designer 3 3 267 Senior Design Engineer 3 3 270 Design Engineer 3 263 5 Tool Designer 4 263 265 Tool Designer 4 158 79 Research and Development Engineer 4 158 114 Research and Development Engineer 4 158 217 Research and Development Manager 4 (15 row(s) affected)
Siehe auch
Konzepte
Verwenden allgemeiner Tabellenausdrücke
Andere Ressourcen
WITH common_table_expression (Transact-SQL)
Abfragehinweis (Transact-SQL)
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
DELETE (Transact-SQL)
EXCEPT und INTERSECT (Transact-SQL)