我有一个父ID的列表,如100,110,120,130这是动态的,可以改变.我希望在一组中获得指定父项的所有后代.为了获得单亲的孩子,我使用了这样的查询：
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数组作为参数的查询,并将所有后代返回到一个集合中.
解决方法
你的想法太难了.
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.IsDescendantOf(parent.PersonHierarchyId) = 1 where Parent.PersonId in (<list of parents>)
注意：在这两种情况下,这可能会很慢,因为它必须为n * m个条目评估IsDescendantOf(其中n是父项列表的基数,m是表的基数).
我最近有一个类似的问题,我通过编写一个表值函数来解决它,给定一个hierarchyId将返回所有父项.让我们看一下使用这种方法解决问题的方法.一,功能：
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
它假定您有一个Numbers表.它们非常有用.如果你没有,请查看接受的答案here.让我们谈谈这个功能一秒钟.从本质上讲,它表示“对于传入的hierarchyId,获取当前级别.然后调用GetAncestor直到您处于层次结构的顶部.”请注意,它可选地返回传入的hierarchyId.在我的情况下,我想考虑一个记录本身的祖先.你可能想要也可能不想做.
转到使用它的解决方案,我们得到类似的东西：
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>)
它可能适用于您,也可能不适合您.试试看吧！
