par: Jayendra Viswanathan | mise à jour: 2018-03-27 | Commentaires (2) | connexes: Plus > Scripts
problème
de nombreuses organisations ont un certain type de hiérarchie pour les processus métier. Quand il s’agit de grandes organisations, la hiérarchie peut devenir très complexe et grande, donc construire une hiérarchie dans un SGBDR est une tâche fastidieuse. Nous devons créer des vues, des curseurs et bientôt, mais l’utilisation d’un CTE dans SQL Server est une meilleure solution pour récupérer des données basées sur la hiérarchie et dans cette astuce, je vais vous montrer comment.,
Solution
Les Expressions de Table communes (Cte) ont deux types, récursive et non récursive. Nous verrons comment le CTE récursif fonctionne avec des exemples dans cette astuce.
un CTE récursif peut être expliqué en trois parties:
- requête D’ancrage: c’est la première instruction exécutée. Cette requête donnera les données de base pour le CTE.
- séparateur: c’est la partie centrale où nous utilisons généralement une UNION ALLand quelques opérateurs de plus.
- requête récursive: c’est la partie principale, c’est la requête CTE qui fait référence au même CTE par récursivité.,
créons un exemple de CTE
prenons un scénario d’un diagramme d’organisation (org). Dans cet exemple, le graphique d’organisation commencerait à partir de » PDG « et se retrouverait au”Département D’achat ». Chaque département / personne est lié aux asnodes prédécesseurs. Voyons comment un CTE peut être utilisé pour y parvenir dans SQL Server. Nous allons égalementtoucher la base sur la façon d’utiliser MAXRECURSION lors de l’utilisation d’un CTE.
nous allons créer un exemple de table.
maintenant, la table est créée et nous pouvons remplir la table avec les valeurs de la table « MyDepartment”.,Les instructions insert ci-dessous peuvent être exécutées pour insérer les données dans la table « MyDepartment”.
Le ParentID de l’arbre le plus haut est défini sur NULL indiquant qu’il n’y a pas de parent pour cette ligne. Maintenant que nous avons chargé les données, nous pouvons coder une requête pour parcourir la hiérarchie en utilisant une Expression de Table commune.
nous devons rendre compte de l’ensemble de la structure organisationnelle sous la direction du PDG. Le code récursif suivant utilisant un CTE nous obtiendra la sortie:
WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)AS( SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree FROM MyDepartment WHERE ParentID IS NULL UNION ALL SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1 FROM MyDepartment JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID) SELECT * FROM OrgTree ORDER BY Tree
ci-dessous est la sortie de l’exécution de la requête ci-dessus.,/td>
Anchor Query
Creating tables is one piece of it, inserting data is another group in the example.,La partie importante est la mise en œuvre du CTE en utilisant la clause WITH. Pour notre exemplele nom du CTE est nommé « OrgTree ». Le premier select dans le Cteest utilisé pour extraire le premier nœud de l’arborescence qui est « CEO” et L’ID parent est défini comme NULL. La requête ci-dessous obtiendra le premier nœud dans notre exemple.
SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree FROM MyDepartmentWHERE ParentID IS NULL
| DepartmentID | Nomduservice | ParentID | Arbre |
|---|---|---|---|
| 1 | chef de la direction | NULL | 0 |
ci-Dessous j’ai expliquer les données., Le côté gauche a les données parent et le côté droit a les données Enfant. Si vous remarquez Departmententid 1 (côté gauche) est le parentfor Departmententid (côté droit) 2 et 3. Si vous regardez plus loin, chaque Departtentid estconnecté avec le ParentID dans la table enfant. Vous trouverez ci-dessous la représentation d’imagede la requête générée ci-dessus. La flèche relie le Departmententid et Parentidpour notre référence.
séparateur et requête récursive
la section suivante est la jointure interne combinant le CTE où en récursivité comesinto image, qui interne se réfère à lui-même., La jointure interne récupère les données en divisant la table” MyDepartment » en deux parties à l’aide de L’OrgTreeCTE et fait une jointure et crée le CTE pour que nous puissions interroger le CTE.
SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1FROM MyDepartmentJOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID
Concepteur de requêtes
l’impression d’écran du Concepteur de requêtes ci-dessous est disponible pour que nous puissions voir la requête dans le concepteur. Le côté droit est le Cte – « OrgTree », puisque CTE sera créé après l’exécution, le concepteur de requêtes n’affiche pas les colonnes, si vous utilisez la table” MyDepartment » avec la colonne et la référence de jointure interne.,
MAXRECURSION
Lorsqu’il s’agit d’utiliser un CTE, l’un des problèmes rencontrés est une boucle infinie lors de la formation du CTE. En général, lorsque la requête parent et enfant renvoie la même valeur orequal, le CTE peut entrer dans une boucle infinie et la transaction peut entrer dans une boucle infinie. Pour éviter cela, il existe une clause option qui peut être utilisée à la fin de la commande CTE SELECT avec le mot clé MAXRECURSION et le nombre de lignes. Using0 n’a pas de restriction, mais dans notre exemple, j’ai utilisé une valeur de 10.,
WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)AS( SELECT DepartmentID, DepartmentName, ParentID, 0 AS Tree FROM MyDepartment WHERE ParentID IS NULL UNION ALL SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID, OrgTree.Tree + 1 FROM MyDepartment JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID)SELECT * FROM OrgTree OPTION (MAXRECURSION 10)
Exemple de Requêtes
Essayez ces exemples de requêtes pour trouver les données et voir si vous pouvez venir avec otherscenarios et comment faire pour interroger les données.
-- return everyone under Program Manager (ParentID = 8)WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)AS( SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree FROM MyDepartment WHERE ParentID = 8 UNION ALL SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1 FROM MyDepartment JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID)SELECT * FROM OrgTree;-- return Vice President (DepartmentID = 4) and direct reports (ParentID = 4)WITH OrgTree (DepartmentID, DepartmentName, ParentID, Tree)AS( SELECT DepartmentID, DepartmentName, ParentID , 0 AS Tree FROM MyDepartment WHERE DepartmentID = 4 UNION ALL SELECT MyDepartment.DepartmentID, MyDepartment.DepartmentName, MyDepartment.ParentID , OrgTree.Tree + 1 FROM MyDepartment JOIN OrgTree ON MyDepartment.ParentID = OrgTree.DepartmentID WHERE MyDepartment.ParentID = 4)SELECT * FROM OrgTree; -- return everyone above Senior Manager (DepartmentID = 6)WITH OrgTree(DepartmentName,ParentID,ReportsTo)AS( SELECT T1.DepartmentName,T2.DepartmentID,T2.DepartmentName FROM MyDepartment T1 INNER JOIN MyDepartment T2 ON T1.ParentID=T2.DepartmentID WHERE T1.DepartmentID=6 UNION ALL SELECT OT.ReportsTo,T2.DepartmentID,T2.DepartmentName FROM OrgTree OT INNER JOIN MyDepartment T1 ON OT.ParentID=T1.DepartmentID INNER JOIN MyDepartment T2 ON T1.ParentID=T2.DepartmentID)SELECT * FROM OrgTree;-- return list with of people with no direct reportsWITH OrgTree(ParentID, DepartmentID, DepartmentName, DepartmentLevel) AS ( SELECT ParentID, DepartmentID, DepartmentName, 0 AS DepartmentLevell FROM MyDepartment WHERE ParentID IS NULL UNION ALL SELECT e.ParentID, e.DepartmentID, e.DepartmentName, DepartmentLevel + 1 FROM MyDepartment AS e INNER JOIN OrgTree AS d ON e.ParentID = d.DepartmentID )SELECT * FROM OrgTree WHERE DepartmentLevel = 5;
prochaines étapes
- les CTEs sont une fonctionnalité puissante de SQL Server. La syntaxe est facile à utiliser etnous pouvons construire n’importe quel type de tables hiérarchiques et de données en fonction de nos besoins en utilisant CTE.,
- jetez un œil à certains de ces autres conseils:
- requêtes récursives utilisant des expressions de Table communes (CTE) dans SQL Server
- Comment utiliser SQL Server CTEs pour rendre votre code T-SQL lisible par les humains
- SQL Server CTE et XML Recursion Script
dernière mise à jour: 2018-03-27
à propos de l’auteur
Jayendra est un chef de projet avec de nombreuses années d’expérience en informatique., Il possède de solides connaissances en développement de logiciels et en gestion de projet.voir tous mes conseils
- Plus de conseils pour les développeurs de bases de données…
