Как вы получаете всех предков node с использованием иерархии SQL Server 2008?

Ответ 1

Наиболее часто используемый подход - это рекурсивное выражение общей таблицы (CTE)

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

(адаптирован из сообщение в блоге Simon Ince)

Саймон Инсе также предлагает второй подход, в котором он просто в основном меняет условие - вместо того, чтобы обнаруживать записи тех людей, которые являются предками целевого человека, он поворачивает чек вокруг:

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

Это выберет все строки из вашей таблицы, где интересующий вас человек будет потомком любого уровня вниз по иерархии. Таким образом, это обнаружит, что целевой человек немедленных и немедленных предков полностью до корня.

Ответ 2

Здесь ответ переместился в один select:

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

Ответ 3

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

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

Ответ 4

Я написал пользовательскую табличную функцию, которая расширяет значение иерархии в своих составляющих предках. Затем выход можно объединить назад в столбце hierarchyid, чтобы получить эти предки специально.

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

Чтобы использовать его:

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