求一分段提取数据的sql写法

declare @t table(DTime datetime,Spd numeric(4,1),Eg int) 

insert into @t values('2008-01-01 01:55:27.000',0 ,0 )

insert into @t values('2008-01-01 02:00:26.000',0 ,0 )

insert into @t values('2008-01-01 02:00:43.000',23, 1) 

insert into @t values('2008-01-01 02:05:42.000',25, 1) 

insert into @t values('2008-01-01 02:07:47.000',26, 1) 

insert into @t values('2008-01-01 02:12:46.000',24, 1) 

insert into @t values('2008-01-01 02:14:51.000',0 ,0 )

insert into @t values('2008-01-01 02:19:50.000',0 ,0 )

insert into @t values('2008-01-01 02:21:27.000',0 ,0 )

insert into @t values('2008-01-01 02:56:35.000',20, 1) 

insert into @t values('2008-01-01 02:58:48.000',23, 1) 

insert into @t values('2008-01-01 03:03:47.000',23, 1) 

insert into @t values('2008-01-01 03:07:25.000',25, 1) 

insert into @t values('2008-01-01 03:12:24.000',0 ,0 )

insert into @t values('2008-01-01 03:12:39.000',0 ,0 )

insert into @t values('2008-01-01 03:17:38.000',0 ,0 ) 



select

    d.Time1 StartDTime,

    d.Time2 EndDTime  ,

    avg(c.Spd) as Spd_Avg

from

    @t c,

    (select

         a.DTime Time1,min(b.DTime) Time2

     from

         (select m.* from @t m where m.Eg=1 and (select top 1 Eg from @t where DTime<m.DTime order by DTime desc)=0) a,

         (select m.* from @t m where m.Eg=1 and (select top 1 Eg from @t where DTime>m.DTime order by DTime asc)=0)  b

     where

         a.DTIme<b.DTime

     group by 

         a.DTime) d

where

    c.DTime between d.Time1 and d.Time2

group by

    d.Time1,d.Time2



/*

StartDTime                     EndDTime                       Spd_Avg           

------------------------------ ------------------------------ ----------------- 

2008-01-01 02:00:43.000        2008-01-01 02:12:46.000        24.500000

2008-01-01 02:56:35.000        2008-01-01 03:07:25.000        22.750000

*/

------------- 添加 id 取模 , 但是这种方法只对 每一个时间段的记录都是相等的.



declare @t table(DTime datetime,Spd numeric(4,1),Eg int) 

insert into @t values('2008-01-01 01:55:27.000',0 ,0 )

insert into @t values('2008-01-01 02:00:26.000',0 ,0 )

insert into @t values('2008-01-01 02:00:43.000',23, 1) 

insert into @t values('2008-01-01 02:05:42.000',25, 1) 

insert into @t values('2008-01-01 02:07:47.000',26, 1) 

insert into @t values('2008-01-01 02:12:46.000',24, 1) 

insert into @t values('2008-01-01 02:14:51.000',0 ,0 )

insert into @t values('2008-01-01 02:19:50.000',0 ,0 )

insert into @t values('2008-01-01 02:21:27.000',0 ,0 )

insert into @t values('2008-01-01 02:56:35.000',20, 1) 

insert into @t values('2008-01-01 02:58:48.000',23, 1) 

insert into @t values('2008-01-01 03:03:47.000',23, 1) 

insert into @t values('2008-01-01 03:07:25.000',25, 1) 

insert into @t values('2008-01-01 03:12:24.000',0 ,0 )

insert into @t values('2008-01-01 03:12:39.000',0 ,0 )

insert into @t values('2008-01-01 03:17:38.000',0 ,0 )



select * from @t



select * into ##temp1 from @t where Eg=1  order by DTime 



select (select count(1) from ##temp as b where a.Dtime>=b.Dtime) as id ,* into ##temp2 from ##temp as a



select * from ##temp2





select t1.DTime as SDTime,t2.DTime as EDTime , (t1.Spd+t2.Spd)/2 as Spd_Avg 

from 

(select id,DTime,Spd from ##temp2 where id%4=1) as t1 left join

(select id, DTime ,spd from ##temp2 where id%4=0) as t2 on t1.id=t2.id-3



drop table ##temp1

drop table ##temp2