HierarchyID:获取父列表的所有后代 [英] HierarchyID: Get all descendants for a list of parents

问题描述

我有一个像这样 100, 110, 120, 130 这样的父 ID 列表，它是动态的并且可以改变.我想在一个集合中获取指定父母的所有后代.为了获得单亲的孩子，我使用了这样的查询:

I have a list of parent ids like this 100, 110, 120, 130 which is dynamic and can change. I want to get all descendants for specified parents in a single set. To get children for a single parent I used such query:

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

不知道如何为多个父母做到这一点.我的第一次尝试是写一些类似几个联合的东西，但我相信应该有更聪明的方法来做到这一点.

Have no idea how to do that for multiple parents. My first try was to write something like several unions, however I'm sure that there should be smarter way of doing this.

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 列表:

P.S. Also I found such query to select list of ids which might be helpful:

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

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

To summarize, my goal is to write query which accepts array of parent IDs as a parameter and returns all their descendants in a single set.

推荐答案

你想太多了.

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 是表的基数).

Note: in both cases, this could be slow as it has to evaluate IsDescendantOf for n*m entries (with n being the cardinality of the list of parents and m being the cardinality of the table).

我最近遇到了一个类似的问题，我通过编写一个表值函数来解决它，给定一个hierarchyId，它会返回所有的父级.让我们看看使用该方法解决您的问题的方法.一、功能:

I recently had a similar problem and I solved it by writing a table-valued function that, given a hierarchyId would return all of the parents. Let's look at a solution to your problem using that approach. First, the function:

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 表格.它们非常有用.如果您没有，请查看已接受的答案 此处.让我们先谈谈这个功能.本质上，它说对于传入的hierarchyId，获取当前级别.然后调用GetAncestor，直到您处于层次结构的顶部.".请注意，它可以选择返回传入的hierarchyId.就我而言，我想将记录视为其祖先.你可能想也可能不想.

It assumes that you have a Numbers table. They're immensely useful. If you don't have one, look at the accepted answer here. Let's talk about that function for a second. In essence, it says "For the passed in hierarchyId, get the current level. Then get call GetAncestor until you're at the top of the hierarchy.". Note that it optionally returns the passed in hierarchyId. In my case, I wanted to consider a record an ancestor of itself. You may or may not want to.

转向使用它的解决方案，我们得到如下结果:

Moving onto a solution that uses this, we get something like:

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>)

它可能适合您，也可能不适合.试试看！

It may or may not work for you. Try it out and see!

这篇关于HierarchyID:获取父列表的所有后代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！