发现用CTE性能很差？解决办法

--Create table Num27000 (n int)

--declare @i int =0
--while(@i<27000)
--begin
--set @i=@i+1
--insert into [Num27000]
--select @i
--end

--create table level1(l1 int) -- have about 7k rows
--create table level2(l2 int) -- have about 2w rows

--;with cte7k as 
--(select n,m=n%4 from Num27000)
--insert into level1
--select n from cte7k where m=1

--;with cte2w as 
--(select n,m=n%4 from Num27000)
--insert into level2
--select n from cte2w where m<>1

select b.l2,(select max(l1) from level1 where l1<b.l2) from level2 b
--about 20s


--create table numspan(st int,en int)

--;with cte1 as
--(select rn=row_number() over (order by l1),l1 from level1)
--,cte2 as
--(select a.l1 as st,b.l1 as en from cte1 a inner join cte1 b on a.rn=b.rn-1)
--insert into numspan
--select * from cte2

select t2.l2,t1.st from level2 t2 inner join numspan t1
on t2.l2 between t1.st and t1.en
--about 2s


with cteA as
(select rn=row_number() over (order by l1),l1 from level1)
,cteB as
(select a.l1 as st,b.l1 as en from cteA a inner join cteA b on a.rn=b.rn-1)
select t2.l2,t1.st from level2 t2 inner join cteB t1
on t2.l2 between t1.st and t1.en
--about 7min

select t2.l2,t1.st from level2 t2 inner join
(
select a.l1 as st,b.l1 as en from 
(select rn=row_number() over (order by l1),l1 from level1)
 a 
inner join 
(select rn=row_number() over (order by l1),l1 from level1)
 b 
 on a.rn=b.rn-1
)t1
on t2.l2 between t1.st and t1.en