Comment puis-je générer une hiérarchie chemin dans SQL qui conduit à un nœud donné?

Dans mon MS SQL 2008 R2 base de données, j'ai ce tableau:

TABLE [Hierarchy]
[ParentCategoryId] [uniqueidentifier] NULL,
[ChildCategoryId] [uniqueidentifier] NOT NULL

J'ai besoin d'écrire une requête qui va générer tous les chemins qui mènent à un Nœud donné.

Permet de dire ça, j'ai l'arbre suivant:

A
-B
--C
-D
--C

Qui est stockée en tant que:

NULL | A
A    | B
A    | D
B    | C
D    | C

Lors de la demande de Chemins pour le C, je voudrais revenir deux chemins (écrit plus ou moins comme ceci):

A > B > C,
A > D > C
La version de SQL Server que vous utilisez? Le mot-clé de recherche que vous êtes à la recherche pour des Requêtes Hiérarchiques. Je ne suis pas trop familier avec SQLServer, mais ce serait une tâche triviale dans Oracle à l'aide de la connect by et start with opérateurs, et comme je sais que les nouvelles versions de sql server ont déjà la plupart des fonctionnalités que l'Oracle a
Ah, j'ai oublié de mentionner la version, je vais l'ajouter à la question, mais c'est MS SQL 2008 R2
Découvrez de table commune récursive des expressions ou tout simplement des requêtes récursives. Il y a des tonnes d'exemple ici sur DONC
Côté bar; Essayé de deviner pourquoi vous voulez savoir tous les parcours disponibles entre les nœuds. Deviné, c'était peut-être de sorte que vous pouvez calculer le chemin le plus court. Si c'est le cas: l'Algorithme de Dijkstra

OriginalL'auteur Dugan | 2013-01-09

  1. 7

    Voici ma solution, Sql Violon

    DECLARE @child VARCHAR(10) = 'C'

    ;WITH children AS
    (

       SELECT 
         ParentCategoryId,
        CAST(ISNULL(ParentCategoryId + '->' ,'')  + ChildCategoryId AS VARCHAR(4000)) AS Path
       FROM Hierarchy
       WHERE ChildCategoryId =  @child
     UNION ALL
       SELECT 
         t.ParentCategoryId,
         list= CAST(ISNULL(t.ParentCategoryId  + '->' ,'')  + d.Path AS VARCHAR(4000))
       FROM Hierarchy t
       INNER JOIN children  AS d
            ON t.ChildCategoryId = d.ParentCategoryId
     )

    SELECT Path 
    from children c
    WHERE ParentCategoryId IS NULL

    De sortie:

    A->D->C 
A->B->C

    Mise à JOUR:

    @AlexeiMalashkevich, pour obtenir l'id, vous pouvez essayer cette

    SQL Violon

    DECLARE @child VARCHAR(10) = 'C'

;WITH children AS
(

   SELECT 
     ParentCategoryId,
     ChildCategoryId  AS Path
   FROM Hierarchy
   WHERE ChildCategoryId =  @child
 UNION ALL
   SELECT 
     t.ParentCategoryId,
     d.ParentCategoryId 
   FROM Hierarchy t
   INNER JOIN children  AS d
        ON t.ChildCategoryId = d.ParentCategoryId
 )

SELECT DISTINCT PATH
from children c
    Vous obtenez le vérifier aussi me présenter à SqlFiddle
    Peut-être vous avez des solution pour obtenir un seul chemin de l'id au lieu du chemin? Bien sûr, je peux l'analyser, mais peut etre qu'il y a de plus beau solution?
    pourriez-vous nous donner un exemple de votre sortie désirée?
    Assurez-vous. Pour l'exemple précédent, il devrait être de 4 lignes 'A' 'D' 'B' 'C'.
    Réponse de mise à jour.

    OriginalL'auteur EricZ

  2. 2

    Solution Possible est d'utiliser l'expression de table commune récursive, comme mentionné par @a_horse_with_no_name:

    CREATE TABLE [Hierarchy](
[ParentCategoryId] CHAR(1) NULL,
[ChildCategoryId] CHAR(1) NOT NULL
);


INSERT INTO Hierarchy
SELECT NULL, 'A' UNION ALL
SELECT 'A', 'B' UNION ALL
SELECT 'A', 'D' UNION ALL
SELECT 'B', 'C' UNION ALL
SELECT 'D', 'C';


WITH CTE AS (
    SELECT 
        ParentCategoryId, ChildCategoryId, 
        CAST(ISNULL(ParentCategoryId,'') + ChildCategoryId AS VARCHAR(255)) [Path] 
    FROM Hierarchy
    WHERE ParentCategoryId IS NULL

    UNION ALL

    SELECT 
        H.ParentCategoryId, H.ChildCategoryId, 
        CAST(C.[Path] + ' > ' + H.ChildCategoryId AS VARCHAR(255)) [Path] 
    FROM Hierarchy H
    INNER JOIN CTE C ON C.ChildCategoryId = H.ParentCategoryId
) SELECT * FROM CTE;
    Vous devriez commencer à mettre les points-virgules à la fin de ces déclarations: sqlblog.com/blogs/aaron_bertrand/archive/2009/09/03/...
    Merci, bon point et le lien, je suis d'accord.
    Cela permettrait d'exclure de haut niveau des parents sans enfants et aurait besoin d'un peu de manipulation pour rendre compte de l'utilisation de uniqueidentifiers que le tableau des types de données, mais la logique générale est la même que ma réponse. Vous me battre pour la réponse en trois minutes, bravo. 🙂
    oui, vous avez raison, ce n'est pas la solution parfaite, mais juste comme un exemple de l'auteur. 3 minutes, c'est comme l'éternité - votre Serveur SQL a effectué milliards d'opérations pendant ce temps 🙂

    OriginalL'auteur Oleksandr Kucher

  3. 0

    C'est une intéressante structure de la hiérarchie. Semble permettre aux parents éventuellement être les enfants de leurs enfants. Si cela devait arriver ce code logique allait se briser, mais tant que ce n'est pas se produire cela devrait fonctionner.

    Create  Function dbo.IdentifyHierarchyPaths (@DeepestChildNode UniqueIdentifier)
Returns @hierarchy Table
(
        Hierarchy Varchar(Max)
)
As
Begin
        ;With   BuildHier As
        (
                Select  Convert(Varchar(Max),h2.ChildCategoryId) As child, Convert(Varchar(Max),h1.ChildCategoryId) + ' > ' +  Convert(Varchar(Max),h2.ChildCategoryId) As hier
                From    Hierarchy h1
                Left    Join Hierarchy h2
                        On  h1.ChildCategoryId = h2.ParentCategoryId
                Where   h1.ParentCategoryId Is Null
                Union   All
                Select  Convert(Varchar(Max),h1.ChildCategoryId) As child, bh.hier + ' > ' +  Convert(Varchar(Max),h1.ChildCategoryId) As hier
                From    BuildHier bh
                Join    Hierarchy h1
                        On  bh.child = h1.ParentCategoryId
        ),      HierWithTopLevel As
        (
                Select  Convert(Varchar(Max),ChildCategoryId) As hierarchy
                From    Hierarchy
                Where   ParentCategoryId Is Null
                Union   
                Select  hier
                From    BuildHier
        )
        Insert  @hierarchy
        Select  hierarchy
        From    HierWithTopLevel
        Where   Right(hierarchy,36) = Convert(Varchar(36),@DeepestChildNode);
        Return;
End;

    OriginalL'auteur Eric J. Price