求一SQL语句

Question

2.如果连续60秒VALUE有大于10的但是也有小于10的,只要有小于10的就从下一条大于10的开始重新计算,小于10之前的就忽略; ID为18的小于10，从ID 19开始重新计算，那ID为21的符合连续60秒VALUE值都是大于10的条件，怎么不在查询结果内？

Answer 1

if object_id('tempdb..#a') is not null drop table #a
go
create table #a
(
   id int,
   code int ,
   [value] int,
   [time]  varchar(20)
)
insert into #a 
select 1,3101,13,'2014-02-21 10:08:06' union all
select 2,3101,15,'2014-02-21 10:08:41' union all
select 3,3101,12,'2014-02-21 10:08:48' union all
select 4,3101,13,'2014-02-21 10:09:08' union all
select 5,3101,14,'2014-02-21 10:20:55' union all
select 6,3101,17,'2014-02-21 10:21:22' union all
select 7,3101,19,'2014-02-21 10:21:33' union all
select 8,3101,13,'2014-02-21 10:21:58' union all
select 9,3101,13,'2014-02-21 10:22:08' union all
select 10,3101,13,'2014-02-21 10:22:22' union all
select 11,3101,12,'2014-02-21 10:23:01' union all
select 12,3101,13,'2014-02-21 10:23:12' union all
select 13,3101,15,'2014-02-21 10:23:22' union all
select 14,3101,13,'2014-02-21 10:23:45' union all
select 15,3101,17,'2014-02-21 10:24:08' union all
select 16,3101,13,'2014-02-21 11:08:06' union all
select 17,3101,13,'2014-02-21 11:08:41' union all
select 18,3101,9,'2014-02-21 11:08:44' union all
select 19,3101,12,'2014-02-21 11:08:49' union all
select 20,3101,13,'2014-02-21 11:09:08' union all
select 21,3101,16,'2014-02-21 11:10:01' 
--开始查询
;with cte as(
 select *,case when (select count(1) from #a where id=a.id-1 and
   datediff(s,[time],a.[time])>60 )=0 and a.[value]>10 then 10 else 11 end 
 as t from #a a   
)   select * from cte a where t=10 and
exists (select 1 from cte where t=11 and datediff(s,[time],a.[time]) <=120 and
datediff(s,[time],(select [time] from cte  where id=a.id+1))>120) 
 union all 
 select * from cte a where t=10  and
exists(select 1 from cte  where t=11 and a.id=id-1 and [value]>10) 
 union all 
select   * from cte a where  a.id=(select max(id) from cte where t=10)
and  datediff(s,(select max(Convert(varchar,[time],120)) from cte where t=11),a.[time])>60
--查询结果
/*
id          code        value       time                 t
----------- ----------- ----------- -------------------- -----------
10          3101        13          2014-02-21 10:22:22  10
4           3101        13          2014-02-21 10:09:08  10
15          3101        17          2014-02-21 10:24:08  10
21          3101        16          2014-02-21 11:10:01  10(4 行受影响)*/

Answer 2

--开始查询
;with cte as(
 select *,case when (select count(1) from #a where id=a.id-1 and
   datediff(s,[time],a.[time])>60 )=0 and a.[value]>10 then 10 else 11 end 
 as t from #a a   
)  ,  cc as( select * from cte a where t=10 and
exists (select 1 from cte where t=11 and datediff(s,[time],a.[time]) <=120 and
datediff(s,[time],(select [time] from cte  where id=a.id+1))>120) 
 union all 
 select * from cte a where t=10  and
exists(select 1 from cte  where t=11 and a.id=id-1 and [value]>10) 
 union all 
select   * from cte a where  a.id=(select max(id) from cte where t=10)
and  datediff(s,(select max(Convert(varchar,[time],120)) from cte where t=11),a.[time])>60
)
select rank() over (order by id) as id,code,value,time from cc
--查询结果
/*
id                   code        value       time
-------------------- ----------- ----------- --------------------
1                    3101        13          2014-02-21 10:09:08
2                    3101        13          2014-02-21 10:22:22
3                    3101        17          2014-02-21 10:24:08
4                    3101        16          2014-02-21 11:10:01(4 行受影响)*/

Answer 3

简化一下--开始查询
;with
    cte as(
 select *,case when (select count(1) from #a where id=a.id-1 and
   datediff(s,[time],a.[time])>60 )=0 and a.[value]>10 then 10 else 11 end 
 as t from #a a   
)  , cc as
(
 select * from cte a where t=10 and
exists (select 1 from cte where t=11 and datediff(s,[time],a.[time]) <=120 and
datediff(s,[time],(select [time] from cte  where id=a.id+1))>120) 
or 
exists(select 1 from cte  where t=11 and a.id=id-1 and [value]>10) 
 or  a.id=(select max(id) from cte where t=10) 
)
select rank() over (order by id) as id,code,value,time from cc

Answer 4

非常感谢,我还需要一个统计到这条结果记录的一个"开始"和"结束"时间,能不能把结束时间也给我计算在内呢?
新开一贴,劳烦t101lian再给看看.http://bbs.csdn.net/topics/390714220

Answer 5

非常感谢,我还需要一个统计到这条结果记录的一个"开始"和"结束"时间,能不能把结束时间也给我计算在内呢?
新开一贴,劳烦t101lian再给看看.http://bbs.csdn.net/topics/390714220