发现用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<>1select 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 cte2select t2.l2,t1.st from level2 t2 inner join numspan t1on t2.l2 between t1.st and t1.en--about 2swith 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 t1on 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)t1on t2.l2 between t1.st and t1.en
