问Tsql递归联接

EN

create table urls (       
    url VARCHAR(200),
    urlRedirect VARCHAR(200)
)

insert into urls select 'URL1','URL2'
insert into urls select 'URL2','URL3'
insert into urls select 'URL3','URL4'
insert into urls select 'URL5','URL6'
insert into urls select 'URL5','URL7'
insert into urls select 'URL6','URL8'
insert into urls select 'URL9','URL10'
insert into urls select 'URL10','URL11'
insert into urls select 'URL11','URL12'
insert into urls select 'URL12','URL9'

; with recursiveCTE as (
select 
    url as topURL, 
    url, 
    urlRedirect, 
    cast(url + '->' + urlRedirect as varchar(max)) as tree, 
    1 as depth,
    0 as infiniteLoop
from urls

union all

select 
    r.topUrl, 
    t.url, 
    t.urlRedirect, 
    cast(r.Tree + '->' + t.urlRedirect as varchar(max)), 
    r.depth + 1,
    case 
        when r.tree like '%' + t.url + '->' + t.urlRedirect + '%'
        then 1
        else 0
    end as infiniteLoop
from 
    recursiveCTE r 
    join urls t 
        on t.URL = r.urlRedirect
        and r.infiniteLoop = 0
),
deepest as (
select 
    *,
    row_number() over (partition by topUrl order by depth desc) as rn
from recursiveCTE
)
select
    topURL,
    tree,
    depth,
    infiniteLoop
from deepest
where rn = 1
order by topURL

with cte as 
(Select url, urlredirect, 1 as lvl 
 from mtable where url='URL1'
 UNION ALL
 Select a.url, a.urlredirect, b.lvl+1
 from mtable a
 inner join cte b on a.url=b.urlredirect
 where a.url<>'URL1')

 Select * from cte