日期:2014-05-18 浏览次数:20613 次
--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