多个父级的父子关系中使用CTE

Question

我有一个如下所示的父子关系表。我想检索所有父或子ID的记录，例如所有祖先和父母以及如果可能的话，深度也要一并返回。 例如，我想找到D家族，它将返回前14行，因为它们都是同一个家族的成员。 可能会有几组这样的家族。我想查询一个成员并想要获取整个家族记录。是否可以使用CTE实现这一点？ 表中记录的家族结构如下：

                      A
                     / \
                    B   C   G   J
                   /     \ / \ / \
              M   D       E   H   K
             / \ /             \ / \
            N   F               I   L


                 R
                 |
                 S   U
                  \ /
                   T

请帮忙。 表格如下：

   Parent   Child
    ------  ------
    A            B
    A            C
    B            D
    D            F
    M            F
    M            N
    C            E
    G            E
    G            H
    J            H
    J            K
    H            I
    K            I
    K            L
    R            S
    S            T
    U            T

感谢您，Himadri。

- user3632629

---

我知道使用递归查询可以检索所有直接后代。我很想看看是否有人有一个解决任意父/子关系完整图的方案。 - Paul Williams

循环图 :) 我最近遇到了这样一个问题。在 rCTE 进展的同时，我卡在了高效地记录遍历历史（并查询它）上。最终采用了这里建议的 WHILE 循环：http://hansolav.net/sql/graphs.html - 我会关注这篇文章，看看会有什么结果。 - MarkD

Answer 1

这个解决方案仅适用于非循环图，因为如果出现循环，递归将会中断。接受提供的数据集...

create table nodes
(
    p char, 
    c char
)
insert into nodes (p, c)
values
    ('A', 'B'), ('A', 'C'), ('B', 'D'), ('D', 'F'), ('M', 'F'), ('M', 'N'), ('C', 'E'),
    ('G', 'E'), ('G', 'H'), ('J', 'H'), ('J', 'K'), ('H', 'I'), ('K', 'I'), ('K', 'L'),
    ('R', 'S'), ('S', 'T'), ('U', 'T')

GO

我们可以通过递归原始节点记录来获取前向树的父节点->子节点。

CREATE VIEW dbo.Tree
AS
    WITH Hierarchy(r, p, c, [Level])
    AS 
    (
        SELECT p AS r,
               p,
               c,
               0 AS [Level]
        FROM dbo.nodes
        UNION ALL
        SELECT n.p AS r,
               t.p,
               t.c,
               t.[Level] + 1
        FROM Hierarchy t
        INNER JOIN dbo.nodes n ON n.c = t.r
        AND n.p != t.p
    )
    SELECT r, p, c, [Level]
    FROM Hierarchy

这将产生以下结果。

r   p   c   Level
A   A   B   0
A   A   C   0
A   C   E   1
A   D   F   2
A   B   D   1
B   D   F   1
B   B   D   0
C   C   E   0
D   D   F   0
G   G   E   0
G   G   H   0
G   H   I   1
H   H   I   0
J   H   I   1
J   K   L   1
J   K   I   1
J   J   H   0
J   J   K   0
K   K   I   0
K   K   L   0
M   M   F   0
M   M   N   0
R   R   S   0
R   S   T   1
S   S   T   0
U   U   T   0

我们还可以反过来，从子元素向上遍历到其父元素。

CREATE VIEW dbo.ReverseTree
AS
    WITH Hierarchy(r, c, p, [Level])
    AS 
    (
        SELECT c AS r,
               c,
               p,
               0 AS [Level]
        FROM dbo.nodes
        UNION ALL
        SELECT n.c AS r,
               t.c,
               t.p,
               t.[Level] + 1
        FROM Hierarchy t
        INNER JOIN dbo.nodes n ON n.p = t.r
        AND n.c != t.c
    )
    SELECT r, c, p, [Level]
    FROM Hierarchy

而从那里得出的结果如下所示。

r   c   p   Level
B   B   A   0
C   C   A   0
D   D   B   0
D   B   A   1
E   C   A   1
E   E   C   0
E   E   G   0
F   F   D   0
F   F   M   0
F   D   B   1
F   B   A   2
H   H   G   0
H   H   J   0
I   I   H   0
I   I   K   0
I   K   J   1
I   H   J   1
I   H   G   1
K   K   J   0
L   K   J   1
L   L   K   0
N   N   M   0
S   S   R   0
T   T   S   0
T   T   U   0
T   S   R   1

这对于像面包屑导航这样的事情非常方便。

- Paul Hatcher

Answer 2

我找到了一个解决方案。但是我在while循环中使用了CTE。如果有其他解决方案，请建议一下。正如我之前提到的一个包含家庭记录或者说图表的表格，我们将其命名为tbl_ParentChild。

以下是我的代码：

Declare @Child varchar(10), @RowsEffected int
Set @Child='D'-----It is the member whose family we want to find

 CREATE Table #PrntChld (Parent varchar(10),Child varchar(10))
 Insert Into #PrntChld
 Select Parent,Child  from tbl_ParentChild MF
 Where MF.Child=@Child or MF.Parent=@Child

 Select @RowsEffected=Count(*) from #PrntChld

 While @RowsEffected>0 
 BEGIN
    ;WITH Prnt(Parent,Child)
        AS
        ( Select M.Parent,M.Child  from tbl_ParentChild M
            Inner Join #PrntChld F On F.Child=M.Child
          UNION ALL
          SELECT e.Parent,e.Child       
              FROM tbl_ParentChild AS E
              INNER JOIN  Prnt AS M                 
                  ON E.Child = M.Parent           
        ),
        PrntChld(Parent,Child)
        AS
        ( Select M.Parent,M.Child  from tbl_ParentChild M
            Inner Join (Select * from Prnt union Select * from #PrntChld) F On M.Parent=F.Parent
          UNION ALL
          SELECT e.Parent,e.Child       
              FROM tbl_ParentChild AS E
              INNER JOIN  PrntChld AS M                 
                  ON M.Child = E.Parent           
        )

    Insert Into #PrntChld
    Select distinct MF.* from PrntChld MF 
    Left Join #PrntChld T On T.Child =MF.Child and T.Parent  = MF.Parent    
    where T.Child is null
    Select @RowsEffected=@@ROWCOUNT

END

Select * from #PrntChld
drop table #PrntChld

- user3632629

Answer 3

我将发布一个带有递归字符串限制的解决方案。对于一个大图来说，我想知道这种方式在性能上是否好，但我认为它并不那么糟糕。基本上，我正在递归地处理关系，记录了到达每个步骤的“路径”，并检查该路径，以便在遇到循环关系时停止。

对于任何想尝试的人，我将发布创建和填充表格的代码：

create table nodes
(
    p char, 
    c char
)
insert into nodes (p, c)
values
    ('A', 'B'), ('A', 'C'), ('B', 'D'), ('D', 'F'), ('M', 'F'), ('M', 'N'), ('C', 'E'),
    ('G', 'E'), ('G', 'H'), ('J', 'H'), ('J', 'K'), ('H', 'I'), ('K', 'I'), ('K', 'L'),
    ('R', 'S'), ('S', 'T'), ('U', 'T')

以下是查询内容：

declare @let char = 'D';
with cte as
(
    --get the node itself if it exists in the table at all
    select 
        top 1 @let as letter, '' as rec_path 
    from 
        nodes 
    where 
        c = @let or p = @let 

    union all
    -- get the direct relations of the node as a starting point
    select 
        case when c = @let then p else c end as letter, 
        cast(@let as varchar(max)) as rec_path
    from 
        nodes 
    where 
        c = @let or p = @let

    union all
    -- get all of the relations recursively until you reach a node you already processed
    select 
        case when c = cte.letter then p else c end as letter, 
        rec_path + cte.letter as rec_path
    from 
        cte
        join nodes on 
            (cte.letter = nodes.c and charindex(cast(nodes.p as varchar(1)), rec_path, 1) = 0 
            or (cte.letter = nodes.p and charindex(cast(nodes.c as varchar(1)), rec_path, 1) = 0))
)
select 
    distinct letter
from 
    cte

希望这对你有所帮助。我知道你的数据实际上并不是由字母组成的，但是可以使用字符串路径或甚至XML来处理ID。

- fortune