sql-server - tabla - transact sql hierarchy

¿Cómo se obtienen todos los ancestros de un nodo con SQL Server 2008 hierarchyid?

Question

Dada una tabla con una columna de tipo hierarchyid , ¿cómo se escribe una consulta para devolver todas las filas que son ancestros de un nodo específico?

Hay una función IsDescendantOf() que es perfecta para obtener los hijos, pero no hay una función correspondiente IsAncestorOf() para devolver antepasados ​​(y la ausencia de una función GetAncestors() parece ser un descuido).

Answer 1

Aquí hay una respuesta enrollada en una sola selección:

SELECT t1.Id.ToString() as Path, t1.Name
    FROM (SELECT * FROM HierarchyTable
        WHERE Name = ''Joe Blow'') t2,
    HierarchyTable t1
    WHERE t2.Id.IsDescendantOf(t1.Id) = 1

Answer 2

El enfoque más comúnmente utilizado sería una expresión de tabla común (CTE) recursiva

WITH Ancestors(Id, [Name], AncestorId) AS
(
      SELECT
            Id, [Name], Id.GetAncestor(1)
      FROM
            dbo.HierarchyTable
      WHERE
            Name = ''Joe Blow''  -- or whatever you need to select that node

      UNION ALL

      SELECT
            ht.Id, ht.[Name], ht.Id.GetAncestor(1)
      FROM
            dbo.HierarchyTable ht
      INNER JOIN 
            Ancestors a ON ht.Id = a.AncestorId
)
SELECT *, Id.ToString() FROM Ancestors

(Adaptado de un post de Simon Ince )

Simon Ince también propone un segundo enfoque en el que básicamente invierte la condición; en lugar de detectar esas entradas de personas que son un antepasado de la persona objetivo, él revierte la verificación:

DECLARE @person hierarchyid

SELECT @person = Id
FROM dbo.HierachyTable
WHERE [Name] = ''Joe Blow'';

SELECT
    Id, Id.ToString() AS [Path], 
    Id.GetLevel() AS [Level],
    Id.GetAncestor(1),
    Name
FROM 
    dbo.HierarchyTable
WHERE 
    @person.IsDescendantOf(Id) = 1

Esto seleccionará todas las filas de su tabla, donde la persona objetivo que le interesa es un descendiente de cualquier nivel en la jerarquía. Así que esto encontrará a los ancestros inmediatos y no inmediatos de la persona objetivo hasta la raíz.

Answer 3

Escribí una función de valor de tabla definida por el usuario que expande un valor de jerarquía en sus ancestros constituyentes. La salida se puede unir de nuevo en la columna hierarchyid para obtener esos ancestros específicamente.

alter function dbo.GetAllAncestors(@h hierarchyid, @ReturnSelf bit)
returns table
as return
 select @h.GetAncestor(n.Number) as h
 from dbo.Numbers as n
 where n.Number <= @h.GetLevel()
  or (@ReturnSelf = 1 and n.Number = 0)

 union all

 select @h
 where @ReturnSelf = 1
go

Para utilizarlo:

select child.ID, parent.ID
from dbo.yourTable as child
cross apply dbo.GetAllAncestors(child.hid, 1) as a
join dbo.yourTable as parent
   on parent.hid = a.h

Answer 4

Declare @hid hierarchyid=0x5D10 -- Child hierarchy id

SELECT
*
FROM 
  dbo.TableName
WHERE 
  @hid.IsDescendantOf(ParentHierarchyId) = 1