Server 2008 R2 -递归SQL -这可能吗？

Question

我有下表：

| Article-Material1 | Article-Material2 |
|-------------------|-------------------|
| article001        | article002        |
| article001        | article003        |
| article001        | material001       |
| material001       |                   |
| article002        | article004        |
| article002        | material002       |
| material002       |                   |
| article003        | material003       |
| material003       |                   |
| article004        | material004       |
| material004       |                   |
| article005        | article010        |
| article005        | article011        |
| article005        | material001       |
| article010        | material005       |
| material005       |                   |
| article011        | article012        |
| article011        | material004       |
| article011        | material006       |
| material006       |                   |
| article012        | material002       |
| article012        | material007       |
| material007       |                   |

我想要达到这样的效果：

article001
    |- article002
        |- article004
            |- material004
        |- material002
    |- article003
        |- material003
    |- material001
article005
    |- article010
        |- material005
    |- article011
        |- article012
            |- material002
            |- material007
        |- material004
        |- material006
    |- material001

我不知道SQL是否能做到这一点。如果不可能，我还能向正确的方向努力吗？

Answer 1

Declare @YourTable table ([Article-Material1] varchar(25),[Article-Material2] varchar(25))
Insert Into @YourTable values
('article001','article002'),
('article001','article003'),
('article001','material001'),
('material001',null),
('article002','article004'),
('article002','material002'),
('material002',null),
('article003','material003'),
('material003',null),
('article004','material004'),
('material004',null),
('article005','article010'),
('article005','article011'),
('article005','material001'),
('article010','material005'),
('material005',null),
('article011','article012'),
('article011','material004'),
('article011','material006'),
('material006',null),
('article012','material002'),
('article012','material007'),
('material007',null)


Declare @Top    varchar(25) = null       --<<  Sets top of Hier Try 'article002' 
Declare @Nest   varchar(25) = '|-----'  --<<  Optional: Added for readability

;with  cte0 as (
       Select ID=[Article-Material2]
             ,Pt=[Article-Material1]
       From   @YourTable
       Where  [Article-Material2] is not null
       Union All
       Select ID=[Article-Material1]
             ,Pt=null
       From   @YourTable
       Where  [Article-Material1] not in (Select [Article-Material2] from @YourTable where [Article-Material2] is not null) )               
,     cteP as (
      Select Distinct
             Seq  = cast(ID as varchar(500))
            ,ID
            ,Pt
            ,Lvl=1
      From   cte0 
      Where  IsNull(@Top,'X') = case when @Top is null then isnull(Pt,'X') else ID end
      Union  All
      Select Seq  = cast(p.Seq+'.'+r.ID+r.Pt as varchar(500))
            ,r.ID
            ,r.Pt
            ,p.Lvl+1
      From   cte0 r
      Join   cteP p on r.Pt = p.ID and r.Pt=p.ID)
     ,cteR1 as (Select *,R1=Row_Number() over (Order By Seq) From cteP )
     ,cteR2 as (Select A.Seq,A.ID,R2=Max(B.R1) From cteR1 A Join cteR1 B on (B.Seq like A.Seq+'%') Group By A.Seq,A.ID )
Select Distinct
       A.R1  
      ,B.R2
      ,A.ID
      ,A.Pt
      ,A.Lvl
      ,Title = Replicate(@Nest,A.Lvl-1) + A.ID
 From cteR1 A
 Join cteR2 B on A.Seq=B.Seq where R2>=R1
 Order By A.R1

返回

​

​

现在，为了好玩，将@Top设置为'article002'，您将得到：

​

​

Answer 2

出于演示的目的，您可以使用以下代码，基于我以前的一篇文章。

SQL Challenge/Puzzle: How to create an ASCII art hierarchy tree with an SQL query?

with        h (id,pid)
            as
            (
                select      [Article-Material2] as id
                           ,[Article-Material1] as pid

                from        mytable

                where       [Article-Material2] is not null

                union all

                select      distinct
                            [Article-Material1]     as id
                           ,null                    as pid

                from        mytable

                where       [Article-Material1] not in (select [Article-Material2] from mytable where [Article-Material2] is not null)
            )       

           ,last_sibling (id)
            as
            (
                select      max (id)
                from        h
                group by    pid
            )

           ,tree (id,branch,path)
            as
            (
                select      h.id
                           ,cast ('' as varchar(max))
                           ,cast (h.id as varchar(max))

                from        h

                where       h.pid is null

                union all

                select      h.id
                           ,t.branch + case when (select 1 from last_sibling ls where ls.id = t.id) = 1 then ' ' else '|' end + '    '
                           ,t.path + '_' + h.id

                from                    tree            t

                            join        h

                            on          h.pid =
                                        t.id
            )

           ,vertical_space (n)
            as
            (
                select      1

                union all

                select      vs.n + 1
                from        vertical_space  vs
                where       vs.n < 2
            )

select      t.branch + case vs.n when 1 then '|____' + ' ' + t.id else '|' end

from                    tree            t

            cross join  vertical_space  vs

order by    t.path
           ,vs.n desc

option      (maxrecursion 0)
;

|
|____ article001
|    |
|    |____ article002
|    |    |
|    |    |____ article004
|    |    |    |
|    |    |    |____ material004
|    |    |
|    |    |____ material002
|    |
|    |____ article003
|    |    |
|    |    |____ material003
|    |
|    |____ material001
|
|____ article005
     |
     |____ article010
     |    |
     |    |____ material005
     |
     |____ article011
     |    |
     |    |____ article012
     |    |    |
     |    |    |____ material002
     |    |    |
     |    |    |____ material007
     |    |
     |    |____ material004
     |    |
     |    |____ material006
     |
     |____ material001