問題描述

我正在處理這個線程: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.

這篇關于HierarchyID:獲取父列表的所有后代 - 不工作的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！