Rekursive Abfragen in SQL Server 2005

 Was ist ein CTE (Common Table Expression) ?

 Ein CTE ist eine temporäre Ergebnismenge, die im Ausführungskontext einer einzelnen Abfrage definiert ist. Sie ähnelt einer abgeleiteten Tabelle (Unterabfrage in der FROM Klausel), da sie ebsonso wie diese nur im Kontext der Abfrage definiert ist und nicht als Objekt in der Datenbank gespeichert wird. Im Unterschied zur abgeleiteten Tabelle kann sie allerdings selbstreferenzierend sein, womit rekursive Abfragen definiert werden können.

Hier zunächst ein einaches Beispiel einer nicht rekursiven CTE:

 

USE AdventureWorks;

GO

WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)

AS

(

    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)

    FROM Sales.SalesOrderHeader

    GROUP BY SalesPersonID

)

SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,

    E.ManagerID, OM.NumberOfOrders, OM.MaxDate

FROM HumanResources.Employee AS E

    JOIN Sales_CTE AS OS

    ON E.EmployeeID = OS.SalesPersonID

    LEFT OUTER JOIN Sales_CTE AS OM

    ON E.ManagerID = OM.SalesPersonID

ORDER BY E.EmployeeID;

GO

Hier ist ein Teil der Ergebnismenge:

 

EmployeeID  NumberOfOrders MaxDate  ManagerID NumberOfOrders MaxDate

———– ————– ———- ——— ————– ———-

268         48             2004-06-01 273       NULL           NULL

275         450            2004-06-01 268       48             2004-06-01

276         418            2004-06-01 268       48             2004-06-01

277         473            2004-06-01 268       48             2004-06-01

 

In der Hauptabfrage wird die CTE zweimal referenziert, um die Bestellungen der Angestellten und deren Vorgesetzten gegenüberzustellen.

 

Was ist ein rekursiver CTE ?

Um hierarchische Abfragen zu erstellen, können rekursive CTEs verwendet werden. Ein rekursiver CTE besteht im einfachsten Fall aus zwei Teilen: einem Anchor member und einem rekursiven member.

Hier folgt die Struktur eines rekursiven CTEs:

 

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

 

Hier folgt der Ablauf der Ausführung:

1.              Zunächst wird zwischen anchor von dem rekursiven member getrennt

2.              der anchor member wird ausgeführt, welcher Ergebnismenge T0 ergibt. (Beginn der Ausführung)

3.              der rekursive member mit Ti als input und Ti+1 als output wird ausgeführt. (Rekursion)

4.              Schritt 3 wird ausgeführt, bis eine leere Ergebnismenge zurückgegeben wird. (Abbruchbedingung)

5.              Rückgabe der Ergebnismenge. Dies ist der UNION ALL von T0 bis Tn.

 

Hier ein 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

 

Gelb markiert ist der Teil, in dem die Rekursion ausgelöst wird, hier refernziert sich der CTE selbst

Hier ist die Ergebnismenge:

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)

 

Der rot markierte Teil ist Ergebnis des Anchor members, der gelb markierte des rekursiven members.

Advertisements