创建递归查询

Question

create table #tbl
(
ID int,
[Object] varchar(1),
[Subject] varchar(1)
)

insert into #tbl
select 1, 'A', 'B' union all
select 2, 'A', 'C' union all
select 3, 'A', 'D' union all
select 4, 'B', 'E' union all
select 5, 'B', 'F' union all
select 6, 'D', 'G' union all
select 7, 'D', 'H' union all
select 8, 'G', 'I' union all
select 9, 'G', 'J' union all
select 10, 'I', 'K' union all
select 11, 'I', 'L'

select *
from #tbl

下面我有一个有数据的关系表。我需要创建一个查询，在数据集中给出结果。当我想获取A包含的所有数据时，我需要转到一个原子值。原子值是叶级。我需要多次循环抛出数据集。A是3倍，B是1，D是2倍。最好的查询技术是什么？我试图使用公共表表达式使用递归查询，但没有得到正确的结果。

 ID OBJECT SUBJECT
  1    A      B
  2    A      C
  3    A      D
  4    B      E
  5    B      F
  6    D      G
  7    D      H
  8    G      I
  9    G      J
  10   I      K
  11   I      L

1. A: B (E，F)，C，D(G (I (K，L)，J)，H) > E，F，C，K，L，J，H
2. B: E、F
3. D: G (I (K，L)，J)，H> K，L，J，H
4. G: I (K，L)，J> K，L，J
5. I: K，L

编辑

期望的表输出：

ID OBJECT SUBJECT
 1    A      E
 2    A      F
 3    A      C
 4    A      K
 5    А      L
 6    А      J
 7    А      H
 8    B      E
 9    B      F
 10   D      K
 11   D      L
 12   D      J
 13   D      H
 14   G      K
 15   G      L
 16   G      J
 17   I      K
 18   I      L

我尝试这个查询：

;with cte as 
(
select id, object, subject
from #tbl

union all

select a.id, a.object, a.subject
from #tbl a
join cte b on a.subject=b.object
)
select *
from cte

有谁可以帮我？

诚挚的问候

Answer 1

我想我理解您的逻辑(它实际上会产生与您想要的输出不同的结果，我仍然认为这是不正确的)，您需要的递归有点不寻常，因为它需要在结尾处进行一个自联接，以删除层次结构中所有不是最低级别的行：

declare @t table(ID int
                ,[Object] varchar(1)
                ,[Subject] varchar(1)
                );
insert into @t values(1,  'A', 'B'),(2,  'A', 'C'),(3,  'A', 'D'),(4,  'B', 'E'),(5,  'B', 'F'),(6,  'D', 'G'),(7,  'D', 'H'),(8,  'G', 'I'),(9,  'G', 'J'),(10, 'I', 'K'),(11, 'I', 'L');

with r as
(
    select ID
            ,[Object]
            ,[Subject]
            ,0 as Hierarchy
            ,row_number() over (order by ID) as rn
    from @t

    union all

    select t.ID
            ,r.[Object]
            ,t.[Subject]
            ,r.Hierarchy + 1
            ,r.rn
    from r
        join @t as t
            on r.[Subject] = t.[Object]
)
select row_number() over (order by r.[Object], r.rn, r.Hierarchy desc, r.[Subject]) as ID
        ,r.[Object]
        ,r.[Subject]
from r
    left join r r2
        on r.[Subject] = r2.[Object]
where r2.[Object] is null
order by ID;

输出：

+----+--------+---------+
| ID | Object | Subject |
+----+--------+---------+
|  1 | A      | E       |
|  2 | A      | F       |
|  3 | A      | C       |
|  4 | A      | K       |
|  5 | A      | L       |
|  6 | A      | J       |
|  7 | A      | H       |
|  8 | B      | E       |
|  9 | B      | F       |
| 10 | D      | K       |
| 11 | D      | L       |
| 12 | D      | J       |
| 13 | D      | H       |
| 14 | G      | K       |
| 15 | G      | L       |
| 16 | G      | J       |
| 17 | I      | K       |
| 18 | I      | L       |
+----+--------+---------+

Answer 2

使用recursive CTE的一种方法(如果不需要订货)

create table #tbl
(
ID int,
[Object] varchar(1),
[Subject] varchar(1)
)

insert into #tbl
select 1, 'A', 'B' union all
select 2, 'A', 'C' union all
select 3, 'A', 'D' union all
select 4, 'B', 'E' union all
select 5, 'B', 'F' union all
select 6, 'D', 'G' union all
select 7, 'D', 'H' union all
select 8, 'G', 'I' union all
select 9, 'G', 'J' union all
select 10, 'I', 'K' union all
select 11, 'I', 'L'

;With
rcte as
(
    -- Anchor member
    select  *, [object] as parent               
    from   #tbl

    union all

    -- Recursive member
    select  c.*, r.parent                 
    from    #tbl c
    inner join rcte r on c.Object = r.Subject
)
Select distinct parent, Subject from rcte x 
where subject not in (Select object from rcte y where x.parent = y.parent)