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

问题描述

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

Given a table with a hierarchyid type column, how do you write a query to return all rows that are ancestors of a specific node?

有一个 IsDescendantOf() 函数，它非常适合获取子级，但是没有相应的 IsAncestorOf() 函数来返回祖先(并且没有GetAncestors() 函数似乎是一个疏忽.)

There is an IsDescendantOf() function, which is perfect for getting the children, but there's no corresponding IsAncestorOf() function to return ancestors (and the absence of a GetAncestors() function seems like quite an oversight.)

推荐答案

最常用的方法是递归公用表表达式 (CTE)

The most commonly used approach would be a recursive Common Table Expression (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 还提出了第二种方法，他只是基本上颠倒了条件 - 他没有检测那些是目标人物祖先的人物条目，而是反过来检查:

Simon Ince also proposes a second approach where he just basically reverses the condition - instead of detecting those person entries that are an ancestor of the target person, he turns the check around:

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

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

This will select all the rows from your table, where the target person you're interested in is a descendant of - any level down the hierarchy. So this will find that target person's immediate and non-immediate ancestors all the way up to the root.

这篇关于如何使用 SQL Server 2008hierarchyid 获取节点的所有祖先?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！