Exemple de CTE et de récursivité du server SQL

Je n'ai pas testé votre code, juste essayé de vous aider à comprendre comment cela fonctionne dans le 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'un CTE récursif que je puisse 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 la valeur est affectée à N à chaque fois, la valeur N peut être incrémentée mais seule la première valeur 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 C'est une syntaxe de preference personnelle. Il existe deux methods principales d'aliasing de colonnes dans un CTE en T-SQL . J'ai inclus l'analogue d'un simple CTE dans Excel pour essayer d'illustrer de façon 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 ici sur le CTE et la récursivité de la relation avec les employés au moment où j'ajoute deux gestionnaires et que j'ajoute quelques employés de plus sous le deuxième gestionnaire, puis le début du problème. Je veux afficher les détails du premier responsable et dans les lignes suivantes, seuls les détails des employés viendront ceux qui sont subordonnés à ce manager

A2:

Est-ce que ce code répond à 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 d'tree

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

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

En termes simples, un CTE récursif peut être défini sémantiquement comme suit:

1: La requête CTE. Aussi connu comme ANCHOR.

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

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

Un petit exemple qui rendra l'image 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 directement aucun autre fournisseur (-1)

La requête récursive de la première itération reçoit tous les fournisseurs qui fournissent les fournisseurs returnnés par l'ANCHOR. Ce process continue jusqu'à ce que la condition renvoie des tuples.

UNION ALL returnne tous les tuples sur les appels récursifs totaux.

Un autre bon exemple peut être trouvé ici .

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 obtenez le point.

Le process d'exécution est vraiment confus avec CTE récursif, j'ai trouvé la meilleure réponse à https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx et le résumé du process d'exécution CTE est comme ci-dessous.

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

1. Diviser l'expression CTE en membres d'ancrage et récursifs.
2. Exécutez le (s) membre (s) d'ancrage en créant le premier jeu d'invocation ou de résultat de base (T0).
3. Exécutez le (s) membre (s) récursif (s) avec Ti en input et Ti + 1 en sortie.
4. Répétez l'étape 3 jusqu'à ce qu'un jeu vide soit renvoyé.
5. Renvoie le jeu de résultats. C'est un UNION ALL de T0 à Tn.

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