sql-server - 查找多父层次结构数据模型中两个节点之间的所有可能关系[SQL Server]

Question

我有一个数据模型来定义多父层次数据。每条记录将代表两个节点的关系，其中一个将是父节点，另一个将是子节点。在我的例子中，一个节点可以有多个父节点。我需要找到两个节点之间所有可能的关系。

例如下表。

---------------------------------
| id | parent_node | child_node |
---------------------------------
| 1  | NULL        | A          |
| 2  | NULL        | B          |
| 3  | A           | C          |
| 4  | A           | D          |
| 5  | B           | D          |
| 6  | B           | E          |
| 7  | C           | G          |
| 8  | C           | H          |
| 10 | D           | I          |
| 11 | E           | I          |
| 12 | E           | J          |
---------------------------------

这将形成如下图

        A   B
       / \ / \    
      C   D   E
    /  \   \ / \
   G    H   I   J

在上面的模型中，A 和 B 将是顶级节点，每个节点都有两个子节点。节点 D 被指定为节点 A 和 B 的子节点。节点 I 也被指定为节点 D 和节点 E 的子节点。所有其他节点只有一个父节点。

我需要编写一个查询来显示一个节点与另一个节点的所有可能关系。例如，

1. A 和 C 有关系，因为 C 是节点 A 的子节点。
2. A 和 D 有关系，因为 D 是节点 A 的子节点。
3. A和G有关系，因为G是节点A的孙节点。
4. 这将适用于任意数量的级别。
5. 如果任何一个节点不是另一个节点的子节点或第 n 级孙节点，则两个节点没有任何关系。

如果两个节点没有任何关系，则不会显示。

上图的最终结果如下，

----------------------------
| parent_node | child_node |
----------------------------
| A           | C          |
| A           | D          |
| C           | G          |
| C           | H          |
| D           | I          |
| A           | G          |
| A           | H          |
| A           | I          |
| B           | D          |
| B           | E          |
| B           | I          |
| E           | I          |
| E           | J          |
| B           | J          |
----------------------------

我是 SQL Server 新手。请帮我解决这个问题。

Answer 1

通过做一些研究，我能够自己编写查询。正如@SeanLange 在评论中指出的那样，这种类型的查询称为递归 CTE。

如果表名是 nodes，下面的查询将创建新表 relationship 并将所有可能的关系存储在其中，如我的问题中所述。

;with cte as (
    select  child_node
    ,       parent_node
    ,       child_node as root
    from    nodes
    union all
    select  child.child_node
    ,       child.parent_node
    ,       parent.root
    from    cte parent
    join    nodes child
    on      parent.parent_node = child.child_node
)
select parent_node,
       root as child_node
into   relationship
from   cte
where  parent_node is not null;

select * from relationship;