如何使用SQL Server 2008 hierarchyid获取节点的所有祖先？

Question

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

有一个 IsDescendantOf() 函数可用于获取子级，但没有对应的 IsAncestorOf() 函数来返回祖先（而缺少一个 GetAncestors() 函数似乎是一个大疏忽）。

- marc esher

---

child.IsDescendantOf(parent) 不就是 parent.IsAncestorOf(child) 吗？ - Gabe

@Gabe 是的，如果 IsAncestorOf(node) 存在，它们将是等效的。这不需要递归查询。 - Mads Nielsen

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的博客文章）

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

这会选择您表中的所有行，其中您感兴趣的目标人物是任何层次结构下的一个后代。因此，它将查找该目标人物的直接和非直接祖先，一直到根。

- marc_s

---

在那篇博客中，紧接着这个CTE解决方案是一个更简单的解决方案（“这个方案可以正常工作，但是它是实现最佳效果的最优方式吗？不是。让我们再试一次！”）吗？ - AakashM

@AakashM：是的，确实有第二个选项 - 从外观上看，这也可以工作，但我可能不会使用它。 - marc_s

我知道这篇文章很旧了，但是我写下这段话是为了未来的读者：当执行计划不存在时，“Simon Ince博客文章中的方法”几乎比“CTE”方法慢100倍。 - Achilles

是的 @Achilles，可能是因为当您将IsDescendantOf函数应用于每行的ID时，查询变得不可索引。我总是用查询获取索引扫描。递归CTE似乎是一个更好的选择。 - Nisarg Shah

Answer 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

- Richard

---

where子句的第一个谓词是多余的，因为父级始终是其自身的后代。 - influent

Answer 3

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

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

- muthuvel

---

即使在hierarchyID上有索引，它也必须为每一行评估IsDesendentOf，不是吗？我认为我有更好的方法（请参见我的答案） - Ben Thul

Answer 4

我编写了一个自定义的表值函数，可以将 hierarchyid 值扩展为其组成的祖先。然后可以通过在 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

- Ben Thul

---

请帮我解决这个问题。http://stackoverflow.com/questions/44016261/how-do-you-get-recursivelevel-using-sql-server-2012-hierarchyid - Manojkanth

我最近必须解决这个问题，我认为这是此处提供的最佳解决方案。将列参考传递给IsDescendantOf（）的其他解决方案必须在查询执行器中解析该谓词，而不是存储引擎。如果您拥有任何数据量，性能将非常糟糕。 - Jason Pease

我已经编写了一个CLR函数来完成相同的事情。 - Ben Thul

我喜欢你的想法。我要借鉴这个点子。 - Jason Pease

Answer 5

完善Ben Thui的答案，我认为这是目前最好的答案...

以下方法允许在一个查询中检索不仅一个，而且可能是多个叶行及其祖先。

Create Or Alter Function dbo.GetAllAncestors
(
    @Path       HierarchyId,
    @WithSelf   Bit = 1,
    @MinLevel   Int = 0,
    @MaxLevel   Int = Null
)
Returns Table
As
Return

With Ancestor As
(
    Select  @Path As Path
    Union All

    Select  Path.GetAncestor(1)
    From    Ancestor
    Where   Path.GetLevel() > 0
)

Select  Path, Path.GetLevel() As Level
From    Ancestor
Where   (@WithSelf = 1 Or Path <> @Path)
And     Path.GetLevel() >= Case When @MinLevel < 0 Or @MinLevel Is Null Then 0 Else @MinLevel End
And     (@MaxLevel Is Null Or Path.GetLevel() <= @MaxLevel)

使用方法：

-- This assumes the table has a Path HierarchyId colum, and the values are unique and indexed.

-- If you know the path
Select *
From MyTable
Where Path In
(
    Select Path From dbo.GetAllAncestors(@ThePath, Default, Default, Default)
)

-- If you don't know the path
Select *
From MyTable t1
Where Path In 
(
    Select Path
    From   MyTable t2
           Cross Apply dbo.GetAllAncestors(t2.Path, Default, Default, Default)
    Where  /* Find the leaf record(s) here.
              Note that if multiple rows match, they will all be returned as well as their parents in a single roundtrip. */
)

- James Dingle

Answer 6

DECLARE @hid_Specific HIERARCHYID 
SET @hid_Specific = '/1/1/3/1/';

SELECT hrchy_id,* FROM tblHierarchyData 
WHERE PATINDEX(hrchy_id.ToString() + '%', @hid_Specific.ToString()) = 1

- Michael Yeh

---

请您能否给您的解决方案添加一些说明？ - wuerfelfreak