HierarchyID:获取父列表的所有后代 - 不工作

我正在处理这个线程:HierarchyID:

但是，该查询对于 ID 列表不是很有用.因此，对于列表，我正在尝试在上面链接的线程中找到答案:

SELECT孩子.*从@Ph 作为父母INNER JOIN @Ph as child on child.ProductHierarchyNode.IsDescendantOf(parent.ProductHierarchyNode) = 1在哪里(4) 中的 parent.ProductHierarchyId

我确定我忽略了一些明显的东西.只是不确定它是什么.但这只会返回父项而没有子项.

无法发现我的错误.

解决方案

我觉得你有这个倒退...你想要 DESCENDENTS，但你要求的东西父是 4...没有任何东西有父共 4 个.

你真的不想要这个吗?

SELECT孩子.*从@Ph 作为父母INNER JOIN @Ph as child on child.ProductHierarchyNode.IsDescendantOf(parent.ProductHierarchyNode) = 1在哪里(1) 中的 parent.ProductHierarchyId

这将返回您说您期望的所有行，并且对我来说更有意义，因为您要求 1(根父级)的所有后代.2 是 1 的孩子/后代，3 是 2 的孩子/后代，4 是 3 的孩子/后代.

WHERE parent.ProductHierarchyId IN (1)"的意思是找到所有 1 是父/祖先的节点".

在第一个查询中，您要求 4 是后代的所有节点，所以这是有道理的.

在第二个查询中，您要查询 1 的所有后代.如果您想要4 的所有祖先"，那将是一个不同的查询.

I'm working off this thread: HierarchyID: HierarchyID: Get all descendants for a list of parents

I have a table that uses a HierarchyID, and I need a query that gives me all descendants for specified parent(s) in a single set.

Here's my table, populated:

DECLARE @Ph TABLE (ProductHierarchyNode HIERARCHYID, ProductHierarchyId INT)
INSERT INTO @Ph (ProductHierarchyNode, ProductHierarchyId) VALUES 
(hierarchyid::Parse('/1/'), 1),
(hierarchyid::Parse('/1/1/'), 2),
(hierarchyid::Parse('/1/1/2/'), 3),
(hierarchyid::Parse('/1/1/2/1/'), 4)

This query works perfectly for a SINGLE id: 4. It gives me back that item, plus all of its descendants.

SELECT
    *
FROM 
    @Ph
WHERE 
    (SELECT ProductHierarchyNode FROM @Ph WHERE ProductHierarchyId = 4).IsDescendantOf(ProductHierarchyNode) = 1 

However, that query isn't very useful for a list of ID's. So for a list, I'm trying the answer in the thread I linked to above:

SELECT 
    child.*
FROM 
    @Ph as parent
    INNER JOIN @Ph as child on child.ProductHierarchyNode.IsDescendantOf(parent.ProductHierarchyNode) = 1
WHERE 
    parent.ProductHierarchyId in (4)

I'm sure I'm overlooking something obvious. Just not sure what it is. But this only returns me the parent item and no children.

Can't spot my error.

解决方案

I feel like you have this backwards... you want DESCENDENTS, but you're asking for things where the parent is 4... nothing has a parent of 4.

Don't you really want this?

SELECT 
    child.*
FROM 
    @Ph as parent
    INNER JOIN @Ph as child on child.ProductHierarchyNode.IsDescendantOf(parent.ProductHierarchyNode) = 1
WHERE 
    parent.ProductHierarchyId in (1)

This returns all the rows you say you're expecting, and makes more sense to me as you're asking for all descendent of 1 (the root parent). 2 is a child/descendent of 1, 3 is a child/descendent of 2, and 4 is a child/descendent of 3.

The "WHERE parent.ProductHierarchyId IN (1)" is saying "find me all nodes where 1 is a parent/ancestor".

In the first query, you're asking for all nodes where 4 is a descendent, so that makes sense.

In the second query, you're asking for all descendents of 1. If you want "all ancestors of 4" that'd be a different query.