Comment interroger les données hiérarchiques, comme une relation employée-gestionnaire, dans SQL?

Les données hiérarchiques de l'interrogation peuvent être obtenues grâce à un CTE récursif. 1. Utilisez un CTE récursif pour étendre la structure de l'arbre pour obtenir tous les subordonnés d'un certain gestionnaire; 2. La même méthode peut interroger réversement tous les supérieurs d'un certain employé; 3. Il faut prendre soin d'éviter les références circulaires, d'optimiser les performances et de considérer les améliorations de la structure de stockage. Plus précisément, l'instruction avec récursive est la couche traversée par couche et combinée à l'indexation, aux restrictions de profondeur et à d'autres moyens d'améliorer l'efficacité.

Interroger des données hiérarchiques, telles que les données des employés-gestionnaire avec une relation père-fils, est en effet un peu plus compliquée que les requêtes ordinaires dans SQL. Parce que ce type de structure est de nature en forme d'arbre et que SQL est conçu pour les tables plates, quelques astuces sont nécessaires pour "développer" l'arbre.

La pratique la plus courante consiste à utiliser une requête récursive , ce que nous appelons souvent le CTE, qui est pris en charge dans les bases de données traditionnelles telles que PostgreSQL, SQL Server, MySQL 8 et Oracle.

Voici plusieurs méthodes pratiques et suggestions de scénarios:

Interrogez toute la hiérarchie en utilisant un CTE récursif

Si vous voulez connaître tous les subordonnés sous un manager (y compris les subordonnés indirects), un CTE récursif est utile.

 Avec un employé récursif_tree comme (
    - Requête initiale: trouver le subordonné direct Sélectionner Employee_ID, Manager_id, nom, 1 AS NIVEAU
    Des employés
    Où manager_id = 100 - Supposons que vous souhaitiez vérifier tous les syndicats subordonnés avec manager_id de 100

    - Partie récursive: Continuez à trouver le niveau suivant SELECT E.EMPLOYEE_ID, E.MANAGER_ID, E.NAME, ET.LEVEL 1
    Des employés e
    Inner Join Employee_Tree et sur e.manager_id = et.employee_id
)
SELECT * dans Employee_Tree;

Cette requête sera recherchée vers le bas par calque jusqu'à ce qu'il n'y ait plus de nœuds enfants. level peut vous aider à comprendre la profondeur de la hiérarchie.

Interroger tous les supérieurs d'un employé (chemin inversé)

Parfois, vous voudrez peut-être vérifier le lien de rapport d'un employé, comme la recherche du PDG de lui tout le long.

Vous pouvez également utiliser un CTE récursif, mais cette fois, vous revenez du nœud enfant au nœud parent:

 Avec récursif manager_chain as (
    - Initial: Commencez avec des employés spécifiques Sélectionnez Employee_ID, Manager_id, nom, 1 As Niveau
    Des employés
    Où Employee_ID = 200 - Je veux vérifier tous les supérieurs de l'employé 200 syndicaux

    - Recursion: recherchez le gestionnaire sélectionné e.employee_id, e.manager_id, e.name, mc.level 1
    Des employés e
    Inner Join Manager_chain MC sur E.Employee_ID = MC.MANAGER_ID
)
SELECT * FROM Manager_Chain;

De cette façon, vous pouvez voir le chemin de l'employé signalant vers le haut.

Optimisation et précautions pour la requête hiérarchique

• Évitez les boucles infinies : s'il y a une référence circulaire dans les données (comme A est le gestionnaire de B et B est le gestionnaire de a), la requête récursive peut être une boucle morte. Un enregistrement de chemin ou une limite de profondeur maximale peut être ajouté lorsqu'il est récursif.

• Problèmes de performance : les requêtes récursives peuvent ralentir lorsque la hiérarchie est profonde ou que la quantité de données est importante. Considérer:

  • Ajouter un index: créer des index appropriés sur manager_id et employee_id
  • Filtres Fields à l'avance: Réduisez les transferts de champ inutiles
  • Contrôlez la profondeur récursive: par exemple, ajoutez WHERE level pour éviter une traversée excessive

• Optimisation de la structure de stockage (avancé) : Si vous souhaitez souvent faire face aux requêtes hiérarchiques, vous pouvez envisager d'introduire un tableau de fermeture ou d'utiliser un modèle de jeu imbriqué, mais ces méthodes sont plus rentables à maintenir.

Fondamentalement, ce sont les méthodes. Bien qu'il soit un peu gênant à écrire que les requêtes ordinaires, tant que la structure est claire et que la logique est claire, les requêtes récursives ne sont en fait pas difficiles à maîtriser. La clé est de choisir la méthode d'écriture appropriée en fonction des besoins de votre entreprise et de faire attention à la conception de la structure des données.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!