如何创建SQL Server 2005 CTE以返回具有多个父级的子级的父子级记录 [英] How to create an SQL Server 2005 CTE to return parent-child records, for children with multiple parents

问题描述

我正在尝试在SQL Server中使用CTE，但是在使以下方案起作用时已经走到了尽头。我有一个与此类似的层次结构表：

I'm experimenting with CTE's in SQL Server but have reached a dead end with getting the following scenario to work. I have a hierarchy table similar to this:

Node(ID:439)
  Node(ID:123)
    Node(ID:900)        
  Node(ID:56)
    Node(ID:900)

预期结果：

NodeID ParentNodeID
439    0
123    439
900    123
56     439
900    56

所以基本上父子层次结构表，有一个细微的差别。每个孩子可能有一个以上的父母。我研究了许多博客文章和StackOverflow帖子，内容涉及创建返回父子记录的CTE，但它们并没有为孩子返回所有父项，只是返回了它找到的第一个。

So basically we have a parent-child hierarchy table, with one subtle difference. Each child could potentially have more then one parent. I have researched many blog articles, and StackOverflow posts, about creating CTE's that return parent-child records, but they don't return all of the parents for the children, just the first one that it finds.

这是我尝试过的示例CTE：

Here's an example CTE that I tried:

WITH Hierarchy(NodeID, ParentNodeID)
AS
(
    SELECT 
        T1.NodeID,
          T1.ParentNodeID
    FROM
        ParentChildTable T1
    WHERE
        T1.NodeID = 439

    UNION ALL
    SELECT 
        T1.NodeID,
        T1.ParentNodeID
    FROM
        Heirarchy T1
        INNER JOIN Heirarchy TH ON TH.NodeID = T1.ParentNodeID
)

（注：出于隐私目的，以上CTE中的表和列已从原始更改了。）

(Note: The names of the tables and columns in the above CTE have been changed from the orginal for privacy purposes.)

上述CTE正常工作，它会找到所有从ID开始的父子记录：439，但只能找到一个括号项目ID：900的t，即使它有两个父母。

The above CTE works fine, it finds all the parent-child records starting from ID:439, but it only finds one parent for item ID:900, even though it has two parents.

有人可以让我知道是否可以使用CTE，或者有其他SQL方法来做到这一点？

Could someone let me know if this is possible using CTE's, or is there another SQL way to do this?

干杯。
Jas。

Cheers. Jas.

推荐答案

一旦我更正了CTE中的语法错误，这对我来说似乎行得通： / p>

This appears to work OK for me, once I corrected the syntax error in your CTE:

create table #ParentChildTable 
(nodeID int not null
,parentNodeID int not null
)

insert #ParentChildTable 
select 900,56
union all select 900,123
union all select 123,439
union all select 56,439
union all select 439,0

;WITH Heirarchy
AS
(
    SELECT 
        T1.NodeID,
          T1.ParentNodeID
    FROM
        #ParentChildTable T1
    WHERE
        T1.NodeID = 439

    UNION ALL
    SELECT 
        T1.NodeID,
        T1.ParentNodeID
    FROM
        #ParentChildTable T1
        INNER JOIN Heirarchy TH ON TH.NodeID = T1.ParentNodeID
)
select *
from Heirarchy

返回结果：

NodeID      ParentNodeID
----------- ------------
439         0
123         439
56          439
900         56
900         123

这篇关于如何创建SQL Server 2005 CTE以返回具有多个父级的子级的父子级记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！