温度/间隔次数/时间的有关问题

create table #t (id varchar(10), Temp float, [time] varchar(5))insert #tselect '01', 101, '9:01'union all select '01', 99, '9:03'union all select '01', 98, '9:04'union all select '01', 95, '9:07'union all select '01', 98, '9:09'union all select '01', 87, '9:11'union all select '01', 88, '9:21'union all select '01', 102, '9:23'union all select '01', 98, '9:25'union all select '01', 103, '9:38'create table #tt (id varchar(10), Temp float, [time] smalldatetime)---------------------------------------------------SET NOCOUNT ONDeclare @id varchar(10), @Temp float, @time smalldatetimeDeclare @status intDeclare @id_0 varchar(10), @Temp_0 float, @time_0 smalldatetime Declare @cnt intSet @id_0 = ''Set @status = 0  -- 0, not previous record, 1, previous record, not ready, 2, previous record readySet @cnt = 0Declare t_cursor Cursor FORSELECT id, Temp, convert(smalldatetime, [time], 108) From #t order by id, convert(smalldatetime, [time], 108)OPEN t_cursorFetch Next From t_cursorINTO @id, @Temp, @timeWHILE @@Fetch_Status = 0Begin    if @id <> @id_0 -- reset everything    Begin        if @status= 2  -- insert prevous record            INsert into #tt select @id_0, @Temp_0, @time_0                 select @cnt=0, @id_0 = '', @status = 0    End    if @Temp > 100 -- temperature satisfied    Begin        -- first do the insertion        if @status > 0 And @cnt > 5 AND Datediff(minute, @time_0, @time)>20 -- good condition        Begin            Insert into #tt select @id_0, @Temp_0, @time_0             set @status = 2  -- the current record also good        End                    Else        Begin            if @status = 2     Insert into #tt select @id_0, @Temp_0, @time_0             set @status = 1  -- this record not good yet        End        select @cnt = 0, @id_0 = @id, @Temp_0 = @Temp, @time_0 = @time -- reset    END    ELSE    Begin -- temperature not satisfied        if @status > 0 set @cnt = @cnt+1    End    FETCH NEXT FROM t_cursor     INTO @id, @Temp, @timeEndClose t_cursorDeallocate t_cursorif @status = 2Begin    INsert into #tt    select @id_0, @Temp, @time_0 EndGO---------------------------------------------select id, Temp, substring(convert(varchar, time, 8), 1, 5) as [time] from #tt    drop table #tdrop table #tt
------解决方案--------------------
SQL code
--测试数据：@Tdeclare @T table(id varchar(2),Temp int,time datetime)insert @Tselect '01',101,'9:01' union allselect '01',99,'9:03' union allselect '01',98,'9:04' union allselect '01',95,'9:07' union allselect '01',98,'9:09' union allselect '01',87,'9:11' union allselect '01',88,'9:21' union allselect '01',102,'9:23'--先把符合条件的区间找出来，同一个id可能存在多个符合条件的区间：select * from @T a join @T b on a.id=b.id and datediff(minute,a.time,b.time)>=20    where a.Temp>=100 and b.Temp>=100        and (select count(*) from @T where id=a.id and time>a.time and time<b.time)>5select distinct a.id from @T a join @T b on a.id=b.id and datediff(minute,a.time,b.time)>=20    where a.Temp>=100 and b.Temp>=100        and (select count(*) from @T where id=a.id and time>a.time and time<b.time)>5/*id01*/