Comment Parcourir un Arbre ou de Travailler Avec des données Hiérarchiques dans le Code SQL

Dire que j'ai un employé de la table, avec un enregistrement pour chaque employé de mon entreprise, et une colonne pour le superviseur (voir ci-bas). Je voudrais préparer un rapport qui énumère les noms et le titre de chaque étape de la surveillance de la ligne. par exemple pour la bite robbins, 1d #15, j'aimerais une liste de chaque superviseur dans sa "chaîne de commandement", tout le chemin pour le président, le grand fromage. Je voudrais éviter d'utiliser les curseurs, mais si c'est la seule façon de faire cela, alors c'est ok.

id  fname   lname   title   supervisorid
1   big     cheese  president   1
2   jim     william vice president  1
3   sally   carr    vice president  1
4   ryan    allan   senior manager  2
5   mike    miller  manager 4
6   bill    bryan   manager 4
7   cathy   maddy   foreman 5
8   sean    johnson senior mechanic 7
9   andrew  koll    senior mechanic 7 
10  sarah   ryans   mechanic    8
11  dana    bond    mechanic    9
12  chris   mcall   technician  10
13  hannah  ryans   technician  10
14  matthew miller  technician  11
15  dick    robbins technician  11

Les données réelles ne sera probablement pas plus de 10 niveaux de profondeur...mais je préfère ne pas juste faire 10 en dehors des jointures...j'espérais qu'il y a quelque chose de mieux que cela, et de moins en moins impliqués que les curseurs.

Merci pour toute aide.

Vous pourriez être intéressé à ma question: stackoverflow.com/questions/3471134/...
Vos données a un petit problème, l'id d'enregistrement de 1 ne peut pas avoir supervisorid=1 il va se casser la CTE de la table

OriginalL'auteur Albert | 2011-07-11

  1. 20

    C'est essentiellement un port de la accepté de répondre à ma question, que j'ai relié à l'OP commentaires.

    vous pouvez utiliser common table expressions

    WITH Family As 
( 
    SELECT e.id, e.supervisorid, 0 as Depth
    FROM Employee e
    WHEREid = @SupervisorID 
    UNION All 
    SELECT e2.ID, e2.supervisorid, Depth + 1
    FROM Employee e2
        JOIN Family 
            On Family.id = e2.supervisorid 
) 
SELECT*
FROM Family

    Pour plus d':

    Requêtes Récursives À L'Aide D'Expressions De Table Communes

    Quel est le caractère @ pour exactement? Vous devez fournir un paramètre? Je n'ai pas été clair, mais ce que j'ai finalement envie de faire est de fournir une liste de tous les employés, et tous leurs "chaînes de commandement"...pour chaque employé, pas seulement un. Pouvez-vous modifier votre requête pour effectuer cela?
    @SupervisorID est si vous voulez avoir une "racine" de personne à une chaîne. Si vous voulez toutes les chaînes, il suffit de déposer la clause where de la ligne tout à fait et cela devrait fonctionner.
    Je pense que le @ SupervisorID est la clé de la récursivité. La deuxième requête à l'intérieur de la CTE (le membre récursif) appelle de nouveau à la Famille avec un id d'employé. La première requête (membre d'ancrage) doit être capable de collecter les employés en vertu de cette donnée d'identification d'employé sur chaque récursion. Depuis la première fois que vous accédez à l'ancre membre, vous n'êtes pas fournir un id d'employé, vous commencez avec le haut niveau, c'est à dire les employés avec une valeur null superviseur id. Si vous n'avez pas que @ SupervisorID, vous vous souhaitez rompre la récursivité.

    OriginalL'auteur Neil N

  2. 0

    Certaines fonction récursive qui soit de retourner le superviseur (le cas échéant) ou la valeur null. Pourrait être un SP qui appelle lui-même, et à l'aide de l'UNION.

    OriginalL'auteur Sunny Milenov

  3. 0

    Vous pourriez être intéressé par le "Chemin Matérialisé" solution, qui n'légèrement de normaliser la table, mais peut être utilisé sur n'importe quel type de base de données SQL et vous évite d'avoir à faire des requêtes récursives. En fait, il peut même être utilisé sur des bases de données SQL.

    Vous avez juste besoin d'ajouter une colonne qui contient l'ensemble de l'ascendance de l'objet. Par exemple, le tableau ci-dessous comprend une colonne nommée tree_path:

    +----+-----------+----------+----------+
| id | value     | parent   | tree_path|
+----+-----------+----------+----------+
|  1 | Some Text |        0 |          |
|  2 | Some Text |        0 |          |
|  3 | Some Text |        2 |       -2-|
|  4 | Some Text |        2 |       -2-|
|  5 | Some Text |        3 |     -2-3-|
|  6 | Some Text |        3 |     -2-3-|
|  7 | Some Text |        1 |       -1-|
+----+-----------+----------+----------+

    La sélection de tous les descendants de l'enregistrement avec l'id=2 ressemble à ceci:

    SELECT * FROM comment_table WHERE tree_path LIKE '-2-%' ORDER BY tree_path ASC

    De créer une arborescence, vous pouvez trier par tree_path pour obtenir un tableau qui est assez facile de convertir à un arbre.

    Vous pouvez également l'indice de tree_path et l'index peut être utilisé lorsque le générique n'est pas au début.

    Par exemple, tree_path LIKE '-2-%' pouvez utiliser l'index, mais tree_path LIKE%-2-" ne le peuvent pas.

    OriginalL'auteur Ali Gangji

  4. 0

    SQL est un langage pour la réalisation de ensemble des opérations et de la récursivité est pas l'un d'eux. En outre, de nombreux systèmes de base de données ont des limitations sur la récursivité à l'aide des procédures stockées comme une mesure de sécurité pour empêcher les voyous du code de la fugue avec de précieuses ressources du serveur.

    Donc, lorsque l'on travaille avec SQL pense toujours "à plat", pas "hiérarchique". Donc, je vous recommande fortement de le 'tree_path' méthode qui a été suggéré. J'ai utilisé la même approche et il fonctionne à merveille et, surtout, très robuste.

    OriginalL'auteur S Chapman