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