sql-server - 如何使用 SQL Server 2008 HierarchyID 获取节点的所有祖先？

Question

给定一个具有 hierarchyid 类型列的表，如何编写查询来返回属于特定节点祖先的所有行？

有一个 IsDescendantOf() 函数，非常适合获取子代，但没有相应的 IsAncestorOf() 函数来返回祖先(并且缺少GetAncestors() 函数似乎是一个很大的疏忽。)

Answer 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 blog post)

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

这将从您的表中选择所有行，其中您感兴趣的目标人员是层次结构中任何级别的后代。因此，这将找到目标人的直系和非直系祖先，一直到根。