Hinweis
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, sich anzumelden oder das Verzeichnis zu wechseln.
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, das Verzeichnis zu wechseln.
Gilt für:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-Datenbank in Microsoft Fabric
Ein gemeinsamer Tabellenausdruck (CTE) bietet den erheblichen Vorteil, sich selbst zu referenzieren, wodurch eine rekursive CTE erstellt wird. Eine rekursive CTE ist eine, in der eine anfängliche CTE wiederholt ausgeführt wird, um Teilmengen von Daten zurückzugeben, bis das vollständige Resultset abgerufen wird.
Eine Abfrage wird als rekursive Abfrage bezeichnet, wenn sie auf eine rekursive CTE verweist. Das Zurückgeben hierarchischer Daten ist eine häufige Verwendung rekursiver Abfragen. Beispielsweise das Anzeigen von Mitarbeitern in einem Organigramm oder Daten in einem Materialabrechnungsszenario, in dem ein übergeordnetes Produkt über eine oder mehrere Komponenten verfügt, und diese Komponenten können Untercomponents aufweisen oder Komponenten anderer übergeordneter Elemente sein.
Ein rekursives CTE kann den Code erheblich vereinfachen, der zum Ausführen einer rekursiven Abfrage in einer SELECT, INSERT, , UPDATE, DELETEoder CREATE VIEW Anweisung erforderlich ist. In früheren Versionen von SQL Server erfordert eine rekursive Abfrage in der Regel temporäre Tabellen, Cursor und Logik, um den Fluss der rekursiven Schritte zu steuern. Weitere Informationen zu allgemeinen Tabellenausdrücken finden Sie unter WITH common_table_expression.
In Microsoft Fabric unterstützen Fabric Data Warehouse und den SQL-Analyseendpunkt standard-, sequenzielle und geschachtelte CTEs, jedoch keine rekursiven CTEs.
Struktur eines rekursiven CTE
Die Struktur der rekursiven CTE in Transact-SQL ähnelt rekursiven Routinen in anderen Programmiersprachen. Obwohl eine rekursive Routine in anderen Sprachen einen Skalarwert zurückgibt, kann eine rekursive CTE mehrere Zeilen zurückgeben.
Ein rekursives CTE besteht aus drei Elementen:
Aufruf der Routine.
Der erste Aufruf des rekursiven CTE besteht aus einer oder mehreren CTE-Abfragedefinitionen, die von
UNION ALL,UNION,EXCEPToderINTERSECTOperatoren verknüpft sind. Da diese Abfragedefinitionen den Basisergebnissatz der CTE-Struktur bilden, werden sie als Ankermm bezeichnet.CTE-Abfragedefinitionen werden als Ankermitglieder betrachtet, es sei denn, sie verweisen auf das CTE selbst. Alle Ankerelementabfragedefinitionen müssen vor der ersten rekursiven Memberdefinition positioniert werden, und ein
UNION ALLOperator muss verwendet werden, um das letzte Ankerelement mit dem ersten rekursiven Element zu verbinden.Rekursives Aufrufen der Routine.
Der rekursive Aufruf enthält eine oder mehrere von Operatoren verknüpfte
UNION ALLCTE-Abfragedefinitionen, die auf das CTE selbst verweisen. Diese Abfragedefinitionen werden als rekursive Member bezeichnet.Beendigungsprüfung.
Die Beendigungsprüfung ist implizit; Rekursion wird angehalten, wenn keine Zeilen aus dem vorherigen Aufruf zurückgegeben werden.
Note
Eine falsch zusammengesetzte rekursive CTE kann eine endlose Schleife verursachen. Wenn beispielsweise die Abfragedefinition des rekursiven Elements für übergeordnete und untergeordnete Spalten die gleichen Werte zurückgibt, entsteht eine Endlosschleife. Beim Testen der Ergebnisse einer rekursiven Abfrage können Sie die Anzahl der rekursiven Ebenen einschränken, die für eine bestimmte Anweisung zulässig sind, indem Sie den MAXRECURSION Hinweis und einen Wert zwischen 0 und 32.767 in der OPTION Klausel der INSERT, UPDATE, , DELETEoder SELECT Anweisung verwenden.
Weitere Informationen finden Sie unter:
Pseudocode und Semantik
Die rekursive CTE-Struktur muss mindestens ein Ankerelement und ein rekursives Element enthalten. Der folgende Pseudocode zeigt die Komponenten eines einfachen rekursiven CTE, das ein einzelnes Ankerelement und ein einzelnes 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 lautet wie folgt:
- Teilen Sie den CTE-Ausdruck in anker- und rekursive Member auf.
- Führen Sie die Ankermitglieder aus, die den ersten Aufruf oder das Basisergebnissatz (
T0) erstellen. - Führen Sie die rekursiven Member
Tials Eingabe undTi+1 als Ausgabe aus. - Wiederholen Sie Schritt 3, bis ein leerer Satz zurückgegeben wird.
- Gibt das Resultset zurück. Dies ist eine
UNION ALLvonT0Tn.
Examples
Das folgende Beispiel zeigt die Semantik der rekursiven CTE-Struktur, indem eine hierarchische Liste von Mitarbeitern zurückgegeben wird, beginnend mit dem höchsten Rang eines Mitarbeiters in der AdventureWorks2025 Datenbank. Eine exemplarische Vorgehensweise der Codeausführung folgt dem Beispiel.
Erstellen einer Mitarbeitertabelle:
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)
);
Füllen Sie die Tabelle mit Werten auf:
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
Beispiel für exemplarische Vorgehensweise für Code
Das rekursive CTE definiert DirectReportsein Ankerelement und ein rekursives Element.
Das Ankerelement gibt das Basisergebnisset T0zurück. Dies ist der höchste Mitarbeiter im Unternehmen. Das heißt, ein Mitarbeiter, der nicht an einen Vorgesetzten meldet.
Hier sehen Sie das vom Ankerelement zurückgegebene Resultset:
ManagerID EmployeeID Title Level
--------- ---------- ----------------------------- ------
NULL 1 Chief Executive Officer 0
Das rekursive Element gibt die direkten untergeordneten Elemente des Mitarbeiters im Ankerelementergebnissatz zurück. Dies wird durch einen Verknüpfungsvorgang zwischen der Tabelle "Mitarbeiter" und dem DirectReports CTE erreicht. Dies ist dieser Verweis auf die CTE selbst, die den rekursiven Aufruf festlegt. Basierend auf dem Mitarbeiter im CTE DirectReports als Eingabe (), gibt die Verknüpfung (TiMyEmployees.ManagerID = DirectReports.EmployeeID) als Ausgabe (Ti + 1) zurück, die Mitarbeiter, die (Ti) als Vorgesetzter haben.
Daher gibt die erste Iteration des rekursiven Elements diesen Resultset zurück:
ManagerID EmployeeID Title Level
--------- ---------- ----------------------------- ------
1 273 Vice President of Sales 1
Das rekursive Element wird wiederholt aktiviert. Die zweite Iteration des rekursiven Elements verwendet das Einzeilen-Resultset in Schritt 3 (mit einem EmployeeID von 273) als Eingabewert und gibt diesen Resultset zurück:
ManagerID EmployeeID Title Level
--------- ---------- ----------------------------- ------
273 16 Marketing Manager 2
273 274 North American Sales Manager 2
273 285 Pacific Sales Manager 2
Die dritte Iteration des rekursiven Elements verwendet das vorherige Resultset als Eingabewert und gibt diesen 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 3
Das endgültige Resultset, das von der ausgeführten Abfrage zurückgegeben wird, ist die Vereinigung aller Vom Anker und rekursiven Member generierten Resultsets.
Hier ist das Ergebnis.
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