Exemple de CTE et de récursion du serveur SQL

Question

Je n'utilise jamais le CTE avec la récursion. J'étais juste en train de lire un article à ce sujet. Cet article montre des informations sur les employés avec l'aide de Sql server CTE et de la récursion. Il s'agit essentiellement de montrer les informations sur les employés et leur manager. Je n'arrive pas à comprendre comment fonctionne cette requête. Voici la requête :

WITH
  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
  AS
  (
    SELECT EmployeeID, FirstName, LastName, ManagerID, 1
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
      r.EmpLevel + 1
    FROM Employees e
      INNER JOIN cteReports r
        ON e.ManagerID = r.EmpID
  )
SELECT
  FirstName + ' ' + LastName AS FullName,
  EmpLevel,
  (SELECT FirstName + ' ' + LastName FROM Employees
    WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports
ORDER BY EmpLevel, MgrID

Je suis en train d'écrire sur la façon dont la sortie est montrée :

J'ai juste besoin de savoir comment il est possible de montrer le manager d'abord et ensuite son subordonné dans une boucle. Je suppose que la première instruction SQL ne s'exécute qu'une fois et qu'elle renvoie tous les numéros d'identification des employés.

Et la deuxième requête se déclenche de manière répétée, en interrogeant la base de données sur l'employé existant avec l'id du manager actuel.

Veuillez m'expliquer comment l'instruction sql s'exécute dans une boucle interne et me dire également l'ordre d'exécution sql. Merci.

MA 2ème phase de question

;WITH Numbers AS
(
    SELECT n = 1
    UNION ALL
    SELECT n + 1
    FROM Numbers
    WHERE n+1 <= 10
)
SELECT n
FROM Numbers

Q 1) Comment la valeur de N est-elle incrémentée ? Si la valeur est attribuée à N à chaque fois, alors la valeur de N peut être incrémentée mais seulement la première fois que la valeur de N a été initialisée.

Q 2) CTE et récursion des relations avec les employés :

Le problème commence au moment où j'ajoute deux managers et quelques employés supplémentaires sous le second manager.

Je veux afficher les détails du premier manager et dans les lignes suivantes, seulement les détails de l'employé qui se rapportent au subordonné de ce manager.

Supposons que

ID     Name      MgrID    Level
---    ----      ------   -----
1      Keith      NULL     1
2      Josh       1        2
3      Robin      1        2
4      Raja       2        3
5      Tridip     NULL     1
6      Arijit     5        2
7      Amit       5        2
8      Dev        6        3

Je veux afficher les résultats de cette manière avec des expressions CTE. S'il vous plaît, dites-moi ce que je dois modifier dans mon sql que j'ai donné ici afin de tirer les relations manager-employé. Merci.

Je veux que la sortie soit comme ceci :

ID          Name   MgrID       nLevel      Family
----------- ------ ----------- ----------- --------------------
1           Keith  NULL        1           1
3           Robin  1           2           1
2           Josh   1           2           1
4           Raja   2           3           1
5           Tridip NULL        1           2
7           Amit   5           2           2
6           Arijit 5           2           2
8           Dev    6           3           2

Est-ce possible... ?

Answer 1

Je n'ai pas testé votre code, j'ai juste essayé de vous aider à comprendre comment il fonctionne en commentaire ;

WITH
  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
  AS
  (
-->>>>>>>>>>Block 1>>>>>>>>>>>>>>>>>
-- In a rCTE, this block is called an [Anchor]
-- The query finds all root nodes as described by WHERE ManagerID IS NULL
    SELECT EmployeeID, FirstName, LastName, ManagerID, 1
    FROM Employees
    WHERE ManagerID IS NULL
-->>>>>>>>>>Block 1>>>>>>>>>>>>>>>>>
    UNION ALL
-->>>>>>>>>>Block 2>>>>>>>>>>>>>>>>>    
-- This is the recursive expression of the rCTE
-- On the first "execution" it will query data in [Employees],
-- relative to the [Anchor] above.
-- This will produce a resultset, we will call it R{1} and it is JOINed to [Employees]
-- as defined by the hierarchy
-- Subsequent "executions" of this block will reference R{n-1}
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
      r.EmpLevel + 1
    FROM Employees e
      INNER JOIN cteReports r
        ON e.ManagerID = r.EmpID
-->>>>>>>>>>Block 2>>>>>>>>>>>>>>>>>
  )
SELECT
  FirstName + ' ' + LastName AS FullName,
  EmpLevel,
  (SELECT FirstName + ' ' + LastName FROM Employees
    WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports
ORDER BY EmpLevel, MgrID

L'exemple le plus simple d'une récursive CTE à laquelle je peux penser pour illustrer son fonctionnement est ;

;WITH Numbers AS
(
    SELECT n = 1
    UNION ALL
    SELECT n + 1
    FROM Numbers
    WHERE n+1 <= 10
)
SELECT n
FROM Numbers

Q 1) Comment la valeur de N est incrémentée. Si une valeur est attribuée à N à chaque fois, alors la valeur de N peut être incrémentée mais seulement la première fois que la valeur de N a été initialisée. .

A1: Dans ce cas, N n'est pas une variable. N est un alias. C'est l'équivalent de SELECT 1 AS N . Il s'agit d'une syntaxe de préférence personnelle. Il existe deux méthodes principales pour aliéner des colonnes dans un fichier de type CTE en T-SQL . J'ai inclus l'analogue d'un simple CTE en Excel pour essayer d'illustrer d'une manière plus familière ce qui se passe.

--  Outside
;WITH CTE (MyColName) AS
(
    SELECT 1
)
-- Inside
;WITH CTE AS
(
    SELECT 1 AS MyColName
    -- Or
    SELECT MyColName = 1  
    -- Etc...
)

Q 2) Maintenant, nous allons parler du CTE et de la récursion de la relation avec les employés. Au moment où j'ajoute deux managers et quelques employés supplémentaires sous le deuxième manager, le problème commence. Je veux afficher les détails du premier manager et dans les lignes suivantes, seuls les détails des employés qui sont subordonnés à ce manager seront affichés.

A2:

Ce code répond-il à votre question ?

--------------------------------------------
-- Synthesise table with non-recursive CTE
--------------------------------------------
;WITH Employee (ID, Name, MgrID) AS 
(
    SELECT 1,      'Keith',      NULL   UNION ALL
    SELECT 2,      'Josh',       1      UNION ALL
    SELECT 3,      'Robin',      1      UNION ALL
    SELECT 4,      'Raja',       2      UNION ALL
    SELECT 5,      'Tridip',     NULL   UNION ALL
    SELECT 6,      'Arijit',     5      UNION ALL
    SELECT 7,      'Amit',       5      UNION ALL
    SELECT 8,      'Dev',        6   
)
--------------------------------------------
-- Recursive CTE - Chained to the above CTE
--------------------------------------------
,Hierarchy AS
(
    --  Anchor
    SELECT   ID
            ,Name
            ,MgrID
            ,nLevel = 1
            ,Family = ROW_NUMBER() OVER (ORDER BY Name)
    FROM Employee
    WHERE MgrID IS NULL

    UNION ALL
    --  Recursive query
    SELECT   E.ID
            ,E.Name
            ,E.MgrID
            ,H.nLevel+1
            ,Family
    FROM Employee   E
    JOIN Hierarchy  H ON E.MgrID = H.ID
)
SELECT *
FROM Hierarchy
ORDER BY Family, nLevel

Un autre sql avec une structure arborescente

SELECT ID,space(nLevel+
                    (CASE WHEN nLevel > 1 THEN nLevel ELSE 0 END)
                )+Name
FROM Hierarchy
ORDER BY Family, nLevel

Answer 2

Je voudrais faire un bref parallèle sémantique à une réponse déjà correcte.

En termes "simples", un CTE récursif peut être sémantiquement défini comme les parties suivantes :

1 : La requête CTE. Également connue sous le nom d'ANCHOR.

2 : La requête CTE récursive sur le CTE dans (1) avec UNION ALL (ou UNION ou EXCEPT ou INTERSECT) de sorte que le résultat final soit retourné en conséquence.

3 : La condition de coin/terminaison. Qui est par défaut lorsqu'il n'y a plus de lignes/tuples retournés par la requête récursive.

Un court exemple qui rendra l'image plus claire :

;WITH SupplierChain_CTE(supplier_id, supplier_name, supplies_to, level)
AS
(
SELECT S.supplier_id, S.supplier_name, S.supplies_to, 0 as level
FROM Supplier S
WHERE supplies_to = -1    -- Return the roots where a supplier supplies to no other supplier directly

UNION ALL

-- The recursive CTE query on the SupplierChain_CTE
SELECT S.supplier_id, S.supplier_name, S.supplies_to, level + 1
FROM Supplier S
INNER JOIN SupplierChain_CTE SC
ON S.supplies_to = SC.supplier_id
)
-- Use the CTE to get all suppliers in a supply chain with levels
SELECT * FROM SupplierChain_CTE

Explication : La première requête CTE renvoie les fournisseurs de base (comme les feuilles) qui ne fournissent pas directement à d'autres fournisseurs (-1).

La requête récursive de la première itération obtient tous les fournisseurs qui approvisionnent les fournisseurs renvoyés par l'ANCRE. Ce processus se poursuit jusqu'à ce que la condition renvoie des tuples.

UNION ALL retourne tous les tuples sur le total des appels récursifs.

Un autre bon exemple peut être trouvé aquí .

PS : Pour qu'un CTE récursif fonctionne, les relations doivent avoir une condition hiérarchique (récursive) sur laquelle travailler. Ex : elementId = elementParentId vous avez compris.

Answer 3

Le processus d'exécution est vraiment déroutant avec les CTE récursifs, j'ai trouvé la meilleure réponse à l'adresse suivante https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx et le résumé du processus d'exécution du CTE est le suivant.

La sémantique de l'exécution récursive est la suivante :

1. Divisez l'expression CTE en membres ancrés et récursifs.
2. Exécuter le ou les membres d'ancrage créant la première invocation ou l'ensemble de résultats de base (T0).
3. Exécuter le(s) membre(s) récursif(s) avec Ti comme entrée et Ti+1 comme sortie.
4. Répétez l'étape 3 jusqu'à ce que vous obteniez un ensemble vide.
5. Retourne le jeu de résultats. Il s'agit d'une UNION TOUS de T0 à Tn.

Answer 4

    --DROP TABLE #Employee
    CREATE TABLE #Employee(EmpId BIGINT IDENTITY,EmpName VARCHAR(25),Designation VARCHAR(25),ManagerID BIGINT)

    INSERT INTO #Employee VALUES('M11M','Manager',NULL)
    INSERT INTO #Employee VALUES('P11P','Manager',NULL)

    INSERT INTO #Employee VALUES('AA','Clerk',1)
    INSERT INTO #Employee VALUES('AB','Assistant',1)
    INSERT INTO #Employee VALUES('ZC','Supervisor',2)
    INSERT INTO #Employee VALUES('ZD','Security',2)

    SELECT * FROM #Employee (NOLOCK)

    ;
    WITH Emp_CTE 
    AS
    (
        SELECT EmpId,EmpName,Designation, ManagerID
              ,CASE WHEN ManagerID IS NULL THEN EmpId ELSE ManagerID END ManagerID_N
        FROM #Employee  
    )
    select EmpId,EmpName,Designation, ManagerID
    FROM Emp_CTE
    order BY ManagerID_N, EmpId