Comment trouver tous les descendants en utilisant HierarchyID pour SQL Server

J'ai besoin de trouver tous les descendants d'une catégorie à l'aide HierarchyID pour SQL Server.

Je sais comment faire pour trouver directement les enfants mais je voudrais trouver les enfants de enfants de les enfants et ainsi de suite.

Est-il un moyen de faire cela en utilisant la HierarchyID?

source d'informationauteur Luke101 | 2010-04-17

  1. 24

    Si vous avez la racine de l'arbre que vous voulez, ne pouvez-vous pas simplement utiliser:

    DECLARE @root hierarchyID;

SELECT @root = col
FROM yourTable
WHERE [whatever uniquely identifies this row]

SELECT *
FROM yourTable
WHERE col.IsDescendantOf(@root) = 1
  2. 16

    Je vais assumer pour mon exemple, que votre table est quelque chose comme ce qui suit:

    DECLARE TABLE MyTable
(
    HID hierarchyid PRIMARY KEY,
    ID int IDENTITY(1, 1),
    SomeText varchar(50)
);

    Si vous voulez que tous les descendants d'un nœud avec l'ID 3, vers le bas à un niveau max (à partir de la racine) de 5:

    DECLARE @searchNode hierarchyid;

SELECT @searchNode = HID
FROM MyTable
WHERE ID = 3;

SELECT *
FROM MyTable
WHERE HID.IsDescendantOf(@searchNode)
AND HID.GetLevel() <= 5;

    Si vous souhaitez 2 niveaux des enfants en vertu de la demande de nœud, vous devrez saisir le niveau de votre recherche nœud dans la première sélectionner et de modifier le rapport à quelque chose comme

    WHERE HID.IsDescendantOf(@searchNode) = 1 
AND HID.GetLevel() <= (@searchLevel + 2);
  3. 0

    Je suis un fan des expressions de table communes pour ce genre de requête parce que vous avez une certaine souplesse dans la possibilité de retourner seulement les enfants, les parents, ou les deux, selon la façon dont vous structurez votre code. Dans ce cas, je suis de retour d'une UNION des deux, juste pour exemple.

    declare @MyTable table
(
    ID int not null,
    HierId hierarchyid null
);

declare @id int
set @id = 1

;with parent (TenantId, HierId, IsParent) as
(
    select
        t.ID
        ,t.HierId
        ,cast(1 as bit) as IsParent
    from @MyTable t
    where t.ID = @id
), children as
(
    select
        t.ID
        ,t.HierId
        ,cast(0 as bit) as IsParent
    from 
        @MyTable t
        inner join parent p
            on t.HierId.IsDescendantOf(p.HierId) = 1
)
select
    *
from parent
UNION
select * 
from children