层次ID：获取一组父节点的所有后代

Question

我有一个类似于 100, 110, 120, 130 这样的父ID列表，它是动态的，可能会发生变化。我想要在一个集合中获取所有指定父级的后代。为了获取单个父级的子项，我使用了以下查询：

WITH parent AS (
    SELECT PersonHierarchyID FROM PersonHierarchy
    WHERE PersonID = 100    
)
SELECT * FROM PersonHierarchy
WHERE PersonHierarchyID.IsDescendantOf((SELECT * FROM parent)) = 1

不知道如何为多个父元素执行此操作。我的第一次尝试是编写几个联合查询，但我确定应该有更聪明的方法来解决这个问题。

SELECT * FROM PersonHierarchy 
WHERE PersonHierarchyID.IsDescendantOf(
    (SELECT PersonHierarchyID FROM PersonHierarchy WHERE PersonID = 100)
) = 1
UNION ALL
SELECT * FROM PersonHierarchy 
WHERE PersonHierarchyID.IsDescendantOf(
    (SELECT PersonHierarchyID FROM PersonHierarchy WHERE PersonID = 110)
) = 1
UNION ALL ...

另外，我还发现了这个查询来选择一系列ID，可能会有所帮助：

SELECT * FROM (VALUES (100), (110), (120), (130)) AS Parent(ParentID)

总之，我的目标是编写查询，接受一个父ID数组作为参数，并返回所有后代在单个集合中。

- Andriy Horen

---

你能使用动态SQL吗？ - Gouri Shankar Aechoor

这个查询将通过支持存储过程的ORM从我的Web应用程序执行。我认为在那里使用动态SQL是可能的。 - Andriy Horen

Answer 1

你想得太复杂了。

WITH parent AS (
    SELECT PersonHierarchyID FROM PersonHierarchy
    WHERE PersonID in (<list of parents>)    
)
SELECT * FROM PersonHierarchy
WHERE PersonHierarchyID.IsDescendantOf((SELECT * FROM parent)) = 1

我会这样写：

select child.*
from PersonHierarchy as parent
inner join PersonHierarchy as child
   on child.PersonHierarchyID.IsDescendantOf(
       parent.PersonHierarchyId
   ) = 1
where Parent.PersonId in (<list of parents>)

注意：在这两种情况下，由于需要对n*m个条目进行IsDescendantOf的评估（其中n是父母列表的基数，m是表的基数），因此可能会很慢。

最近我遇到了类似的问题，并通过编写一个返回所有父项的层次结构ID的表值函数来解决它。让我们使用这种方法来解决您的问题。首先，是该函数：

CREATE FUNCTION [dbo].[GetAllAncestors] (@h HierarchyId, @IncludeSelf bit)
RETURNS TABLE
AS RETURN

    WITH cte AS (
        SELECT @h AS h, 1 AS IncludeSelf
    )
    SELECT @h.GetAncestor(n.NumberId) AS Hierarchy
    FROM ref.Number AS n
    WHERE n.NumberId <= @h.GetLevel()
    AND n.NumberId >= 1

    UNION ALL

    SELECT h
    FROM cte
    WHERE IncludeSelf = @IncludeSelf

它假设您拥有一个数字表。它们非常有用。如果没有，可以查看这里的被接受的答案。让我们来谈谈那个函数。本质上，它说“对于传入的层次结构ID，请获取当前级别。然后一直调用GetAncestor直到达到层次结构的顶部。”请注意，它可选返回传入的层次结构ID。在我的情况下，我希望将记录视为其自身的祖先。您可能想也可能不想这样做。

进入使用此解决方案的内容，我们得到类似以下内容：

select child.*
from PersonHierarchy as child
cross apply [dbo].[GetAllAncestors](child.PersonHierarchyId, 0) as ancestors
inner join PersonHierarchy as parent
  on parent.PersonHierarchyId = ancestors.Hierarchy
where parent.PersonId in (<list of parents>)

这个方法可能适用于你，也可能不适用。试一试，看看会发生什么！

- Ben Thul

---

我的层级结构太小了，不会引起任何性能问题，它不应该超过200条记录，所以现在第一种方法就可以很好地工作。谢谢你的帮助。 - Andriy Horen

想想你发布的第二个查询（不包括CTE），应该把child表连接起来的条件写成：child.PersonHierarchyId.IsDescendantOf(parent.PersonHierarchyId) = 1。 - obaylis

我知道这已经很老了。但是...如果你还在附近，能否看一下这个：https://dev59.com/OLXna4cB1Zd3GeqPEhEJ - Casey Crookston

Answer 2

这对某些人可能很有用。我通过自连接查询找到了一种做法:

SELECT p2.* FROM PersonHierarchy p1
LEFT JOIN PersonHierarchy p2 
    ON p2.PersonHierarchyID.IsDescendantOf(p1.PersonHierarchyID) = 1
WHERE 
    p1.PersonID IN (100, 110, 120, 130)

- Andriy Horen

---

我知道这个问题比较旧了。但是...如果您还在关注这个话题，是否可以看一下这个：https://dev59.com/OLXna4cB1Zd3GeqPEhEJ - Casey Crookston

Answer 3

您可以使用此查询。

Select 
   child.*, 
   child.[PersonHierarchyID].GetLevel(),
   child.[PersonHierarchyID].GetAncestor(1)
From 
   PersonHierarchy as parents
   Inner Join PersonHierarchy as child
               On child.[PersonHierarchyID].IsDescendantOf(parents.[PersonHierarchyID] ) = 1
Where
   parents.[PersonHierarchyID] = 0x68

- Fred

---

我知道这是老问题。不过，如果您还在附近，不介意看一下这个吗：https://dev59.com/OLXna4cB1Zd3GeqPEhEJ - Casey Crookston

Answer 4

请检查，这应该对您有用。我没有尝试修改您的脚本，只是将查询放入了循环中。希望它能帮到您。

DECLARE @String VARCHAR(MAX) = '100, 110, 120, 130'
DECLARE @SQL VARCHAR(MAX)

SET @String = REPLACE(@String, CHAR(32), '') + ','

WHILE CHARINDEX(',', @String) > 0
    BEGIN
       DECLARE @ToString INT
       DECLARE @StringLength INT
       DECLARE @WorkingString VARCHAR(MAX)
       DECLARE @WorkingLength INT

       SET @ToString = CHARINDEX(',', @String)
       SET @StringLength = LEN(@String)
       SET @WorkingString = SUBSTRING(@String, 1, @ToString - 1)

       SET @String = SUBSTRING(@String, @ToString + 1, @StringLength)

       SET @WorkingString =  'SELECT * FROM PersonHierarchy ' + CHAR(13) + CHAR(10) 
                       + 'WHERE PersonHierarchyID.IsDescendantOf((SELECT PersonHierarchyID FROM PersonHierarchy WHERE PersonID = ' 
                       + @WorkingString + ')) = 1' + CHAR(13) + CHAR(10) 
                       + CASE WHEN CHARINDEX(',', @String) > 0 THEN 'UNION ALL'+ CHAR(13) + CHAR(10) ELSE '' END
       SET @SQL = ISNULL(@SQL,'') + @WorkingString
    END
PRINT @SQL
EXEC (@SQL)

- Gouri Shankar Aechoor