Je cherche à créer une requête hiérarchique à l'aide de CTE. Cependant, il me manque quelque chose sur la clause where, la requête doit renvoyer la hiérarchie complète d'un employé
Voici un violon SQL que j'ai créé.
Résultats attendus:
Pour id = 3 code > Je devrais obtenir ces résultats:
ParentEmpId Id Name ----------- ----------- ----- NULL 1 A 1 2 B 2 4 D 2 5 E
Pour id = 2 , je devrais obtenir ces résultats:
ParentEmpId Id Name ----------- ----------- ----- NULL 1 A 1 3 C 3 6 F
3 Réponses :
Le CTE actuel que vous utilisez génère la hiérarchie décroissante du id donné, et non la hiérarchie parent .
Une solution serait de créer un deuxième CTE récursif pour générer la hiérarchie parent, puis UNION les deux, comme suit:
ParentEmpId Id Name Level (null) 1 A 1 1 2 B 0 2 4 D 1 2 5 E 1
Dans ce mis à jour DB Fiddle , lorsqu'il est donné id = 2 code >, la requête renvoie:
WITH EmpCTE(ParentEmpId, Id, [Name], [Level]) AS
(
    SELECT ParentEmpId, Id, [Name], 0 AS [Level]
    FROM emp
WHERE id = 2
   UNION ALL
    SELECT E.ParentEmpId, E.Id, E.[Name], [Level] + 1
    FROM emp E
        INNER JOIN EmpCTE empCTE
        ON E.ParentEmpId = EmpCTE.id
),
EmpCTE2(ParentEmpId, Id, [Name], [Level]) AS
(
    SELECT ParentEmpId, Id, [Name], 0 AS [Level]
    FROM emp
WHERE id = 2
   UNION ALL
    SELECT E.ParentEmpId, E.Id, E.[Name], [Level] + 1
    FROM emp E
        INNER JOIN EmpCTE2 empCTE2
        ON E.id = EmpCTE2.ParentEmpId
)
SELECT * FROM EmpCTE
UNION
SELECT * FROM EmpCTE2
ORDER BY [name]
D'après votre résultat attendu, il semble que vous ayez besoin à la fois d'enfants et de parents d'un id . Par conséquent, vous avez besoin d'une hiérarchie pour les enfants et une pour les parents:
WITH EmpCTE(ParentEmpId, Id, [Name], [Level]) AS
(
    SELECT ParentEmpId, Id, [Name], 0 AS [Level]
    FROM emp
WHERE id=3
        UNION ALL
    SELECT E.ParentEmpId, E.Id, E.[Name], [Level] + 1
    FROM emp E
        INNER JOIN EmpCTE empCTE
        ON E.Id = EmpCTE.ParentEmpId
),
EmpCTE2(ParentEmpId, Id, [Name], [Level]) AS
(
    SELECT ParentEmpId, Id, [Name], 0 AS [Level]
    FROM emp
WHERE id=3
        UNION ALL
    SELECT E.ParentEmpId, E.Id, E.[Name], [Level] + 1
    FROM emp E
        INNER JOIN EmpCTE2 empCTE2
        ON E.ParentEmpId = EmpCTE2.Id
)
Select * from (
select * from EmpCTE
Union
select * from EmpCTE2 ) a
order by name
Pourquoi pas de cette façon?
select data.*
from emp e
cross apply (
select *
from emp e1
where e1.Id=e.Id
union all
select *
from emp e2
where e2.ParentEmpId=e.Id
union all
select e4.*
from emp e3
join emp e4
on e3.ParentEmpId=e4.Id
where e3.Id=e.Id
) data
where e.Id=3
Essayez-vous d'avoir à la fois les enfants et les parents d'un
id? car si vous n'avez besoin que de la hiérarchie des parents d'unid, la sortie serait différente de votre sortie attendue.Il existe différentes manières de procéder. Le moyen le plus simple serait peut-être d'avoir un CTE héritier pour les enfants d'un ID et un CTE hiérarchique pour les parents d'un ID, puis d'unir les CTE.
@rad: oui, j'essaye d'avoir des parents et des enfants pour un certain je.
@ZLK: c'est logique, est-ce la seule solution?
Non, mais c'est peut-être le moyen le plus simple. D'autres auxquels je peux penser nécessiteraient de toute façon de boucler deux fois sur un seul CTE (une fois pour trouver tout ce qui est lié à un identifiant et une fois pour extraire les données). Par exemple, vous pouvez ajouter une colonne au CTE qui contient une liste séparée par des virgules de tous les identifiants de la séquence hiérarchique, puis effectuer une division de chaîne sur celle-ci pour extraire les identifiants, puis rejoindre le CTE (ou la table d'origine si vous ne ne vous souciez pas des niveaux). Il y a peut-être un moyen de le rendre plus efficace que deux CTE, mais deux CTE est une OMI plus simple.
Voici un exemple rapide de la façon dont on pourrait adopter cette approche (bien qu'il existe probablement des moyens d'obtenir de meilleures performances avec des idées similaires): sqlfiddle.com / #! 18 / fee35 / 82
Alors que mon esprit vagabondait au déjeuner, j'ai également pensé que vous pouviez le faire avec le type de données
hierarchyid. Par exemple, une autre solution possible peut être trouvée ici: sqlfiddle.com/#!18/fee35/90 (bien que pratiquement, à moins que votre table ne soit d'une taille assez importante, peu importe la méthode que vous utilisez en termes de performances - il s'agit davantage de savoir laquelle est la plus lisible / intuitive pour vous si vous devez le faire entretien).@ZLK: La dernière requête est fantastique, le niveau fonctionne bien. Merci