用递归选择返回所有子代 [英] Returning all children with a recursive select

问题描述

大家好！我有一张图.首先，我知道如何构建简单递归选择.我阅读了有关 msdn 的信息.

Good day everyone! I've got a graph. First, I know how to build simple recursive selections. I read some info on msdn.

在此图像中，您可以看到，例如，图的顶部节点编号为0，会影响节点编号1(例如(2-> 4)，(3-> 4)，(4-> 5)， (5-> 6)，(1-> 5))

In this image you can see that (for example) the top node of the graph, which is numbered 0, influences node number 1 (etc (2->4), (3->4), (4->5), (5->6), (1->5))

任务:对于每个节点，显示受其影响的节点.例如， 1号影响5和6.

TASK: for every node show nodes which it influences. For example, number 1 influences 5 and 6.

结果SQL必须返回如下内容:

The result SQL must return something like this:

 who_acts| on_whom_influence 
 0       | 1
 0       | 5
 0       | 6
 1       | 5
 1       | 6
 2       | 4
 2       | 5
 2       | 6
 3       | 4
 3       | 5
 3       | 6
 4       | 5
 4       | 6
 5       | 6

我可以使用 CTE的锚成员是:

who_acts| on_whom_influence 
2       | 4
3       | 4
4       | 5
5       | 6
1       | 5
0       | 1

我可以使用SQL语法和递归选择进行选择吗?我该怎么做?

Can I make this selection using SQL syntax and a recursive select? How can I do it?

推荐答案

这听起来像是简单的CTE.您可以在单独的栏中传递影响的根源:

That sounds like a straightforward CTE. You can pass along the root of the influence in a separate column:

; with  Influence as
        (
        select  who_acts
        ,       on_whom_influence
        ,       who_acts as root
        from    dbo.YourTable
        union all
        select  child.who_acts
        ,       child.on_whom_influence
        ,       parent.root
        from    Influence parent
        join    dbo.YourTable child
        on      parent.on_whom_influence = child.who_acts
        )
select  root
,       on_whom_influence
from    Influence
order by
        root
,       on_whom_influence

关于SQL Fiddle的示例.

这篇关于用递归选择返回所有子代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！