Requêtes récursives CTE

Question

Je suis assis avec un exemple tiré du livre "Murach's SQL Server 2016 for developers". L'exemple illustre comment coder des CTS récursifs en SQL. Je connais bien les fonctions de récursion (en C#) mais je n'arrive pas à comprendre comment fonctionne la logique de récursion en SQL. Voici l'exemple :

USE Examples;

WITH EmployeesCTE AS
(
        -- Anchor member
        SELECT EmployeeID, 
            FirstName + ' ' + LastName As EmployeeName, 
            1 As Rank
        FROM Employees
        WHERE ManagerID IS NULL
    UNION ALL
        -- Recursive member
        SELECT Employees.EmployeeID, 
            FirstName + ' ' + LastName, 
            Rank + 1
        FROM Employees
            JOIN EmployeesCTE
            ON Employees.ManagerID = EmployeesCTE.EmployeeID
)
SELECT *
FROM EmployeesCTE
ORDER BY Rank, EmployeeID;

Cette requête renvoie un niveau hiérarchique des employés de l'organisation.

Ma question : Dans une fonction de récurrence, on voit une variable décrémentée qui met fin à la récurrence (en atteignant un cas de base). Ma question est la suivante : où se trouve la partie correspondante dans EmployeesCTE ? Aidez-moi à comprendre la logique.

Answer 1

Ainsi, ce que nous appelons "ETC récursif" devrait en réalité être appelé "ETC itératif". L'idée est que pour définir une table récursive ( EmployeesCTE dans ce cas), nous commençons par créer quelques lignes initiales, dans ce cas cela est fait par

   SELECT EmployeeID, 
        FirstName + ' ' + LastName As EmployeeName, 
        1 As Rank
    FROM Employees
    WHERE ManagerID IS NULL

(il est à noter qu'il n'y a pas de référence aux EmployeesCTE donc ce n'est pas récursif), puis nous itérons une expression, dans ce cas-ci

    SELECT Employees.EmployeeID, 
        FirstName + ' ' + LastName, 
        Rank + 1
    FROM Employees
        JOIN EmployeesCTE
        ON Employees.ManagerID = EmployeesCTE.EmployeeID

pour générer quelques lignes supplémentaires. Nous procédons ainsi jusqu'à ce que l'expression ne renvoie aucune ligne. Dans cette expression EmployeesCTE fait référence à la version précédente de ce tableau, et en l'évaluant, nous calculons la version suivante de ce tableau.

La condition qui arrête la récursion (ou plutôt l'itération) est donc que l'expression récursive n'a produit aucune nouvelle ligne.

Examinons maintenant de plus près comment tous ces éléments s'appliquent à l'exemple particulier que vous avez donné. Notre premier ensemble de lignes est constitué des salariés qui n'ont pas de responsable (nous les appelons les salariés de rang 1). Ensuite, nous trouvons tous les employés qui sont gérés par les employés trouvés à l'étape précédente (nous les appelons employés de rang 2). Ensuite, nous trouvons les employés qui sont gérés par les employés de rang 2 et nous les appelons rang 3, et ainsi de suite. Finalement, nous atteindrons une étape où aucun nouveau salarié ne peut être trouvé (en supposant bien sûr que la relation "géré par" n'a pas de cycles).

Answer 2

Comme vous êtes familier avec le langage C#, vous pouvez considérer ceci comme un complexe modèle d'objet .

Imaginez un simple formulaire Windows.Forms.Form avec ses contrôles. Chaque contrôle possède sa propre collection de contrôles. Dans une base de données, vous pourriez imaginer une table auto-référencée, où chaque ligne pointe vers sa ligne parentale (l'objet supérieur pointe vers NULL), comme vos employés pointent vers leur prochain patron dans la hiérarchie.

Il existe un objet supérieur avec une méthode Refresh() . Lorsque vous appelez cette fonction, elle fait quelque chose sur son propre contenu et appelle Refresh() sur sa collection intérieure. La collection appelle Refresh() sur tous ses membres. Tous font quelque chose et appellent Refresh() sur leurs collections intérieures. On descend ainsi le long du modèle imbriqué jusqu'à ce que l'on atteigne Controls avec une collection Controly vide.

Il s'agit plutôt d'un cascade descendante . Il peut être assez difficile d'arrêter intentionnellement un ETC récursif avec une condition, car vous n'obtiendrez pas la dernière ligne qui contient la condition de rupture.

La deuxième partie de l'ETC récursif s'achève naturellement, lorsque la fonction JOIN ne renvoie aucune ligne...

Dans votre cas, vous pourriez lire ceci comme suit

• anchor : recherche tous les employés qui n'ont pas de patron (niveau le plus élevé)
• Demandez maintenant à la liste tous les salariés dont l'un d'entre eux est le supérieur hiérarchique (deuxième niveau).
• Descendre ligne par ligne et récupérer tous les employés dont le responsable est la personne de deuxième niveau.
• Continuer jusqu'à ce qu'il n'y ait plus de salariés dépendants

Et soyez conscient du fait qu'un ETC récursif est - par conception - une approche lente, puisqu'il s'agit d'un caché RBAR .