6,108
社区成员
发帖
与我相关
我的任务
分享
with cte1 as
(select 1 as lvl, oldsubsid,newsubsid from test1 a where not exists(select 1 from test1 b where a.oldsubsid=b.newsubsid)
)
,cte2(lvl,oldid,newid) as (
select * from cte1
union all
select a.lvl+1 as lvl,a.oldid,b.newsubsid from CTE2 a,test1 b where a.newid=b.oldsubsid
)
select c2.* from cte2 c2
inner join (select oldid,MAX(lvl) as lvl from cte2 group by oldid) cm
on c2.oldid=cm.oldid
and c2.lvl=cm.lvl
order by c2.oldid
我在SQL Server 上试了试可行。 希望高人有好方法