求教sql语句

Question

时间                       val
2011-1-1 0:10:01           0.1
2011-1-1 0:20:01           0.5
2011-1-1 0:30:01           1.1
2011-1-1 0:40:01           1.3
2011-1-1 0:50:01           1.5
2011-1-1 1:00:01           1.7
2011-1-1 1:10:01           0.8
2011-1-1 1:20:01           1.6
2011-1-1 1:30:01           0.8查出连续时间段内val超过1并大于3次的语句，希望高手指教

Answer 1

declare @t table (t datetime,v float)
insert into @t
select '2011-1-1 0:10:01',0.1 union all
select '2011-1-1 0:20:01',0.5 union all
select '2011-1-1 0:30:01',1.1 union all
select '2011-1-1 0:40:01',1.3 union all
select '2011-1-1 0:50:01',1.5 union all
select '2011-1-1 1:00:01',1.7 union all
select '2011-1-1 1:10:01',0.8 union all
select '2011-1-1 1:20:01',1.6 union all
select '2011-1-1 1:30:01',0.8;with t1 as 
(
select row_number() over(order by t) as r, t,case when v>1 then 1 else 0 end as v from @t
),
t2 as 
(
select * from t1 where v = 1
),
t3 as 
(
select row_number() over(order by r) as r2, * from t2 
)
,
t4 as 
(
select  r-r2 as g,t,v 
from t3
)select min(t) as st,max(t) as et,count(g) as times from t4
group by g

Answer 2

create table tb(dt datetime,val decimal(18,2))
insert into tb values('2011-1-1 0:10:01', 0.1)
insert into tb values('2011-1-1 0:20:01', 0.5)
insert into tb values('2011-1-1 0:30:01', 1.1)
insert into tb values('2011-1-1 0:40:01', 1.3)
insert into tb values('2011-1-1 0:50:01', 1.5)
insert into tb values('2011-1-1 1:00:01', 1.7)
insert into tb values('2011-1-1 1:10:01', 0.8)
insert into tb values('2011-1-1 1:20:01', 1.6)
insert into tb values('2011-1-1 1:30:01', 0.8)
go
select min(dt) 开始时间, max(dt) 结束时间 from
(
select m.* , m.px - ((select count(1) from 
(
  select t.* ,(select count(1) from tb where dt < t.dt) + 1 px from tb t
) n where val > 1 and n.px < m.px) + 1) px2 from
(
  select t.* ,(select count(1) from tb where dt < t.dt) + 1 px from tb t
) m
) k
group by px2 having count(1) >= 4drop table tb/*
开始时间                                                   结束时间                                                   
------------------------------------------------------ ------------------------------------------------------ 
2011-01-01 00:30:01.000                                2011-01-01 01:10:01.000（所影响的行数为 1 行）
*/

Answer 3

我这个还有点错,更改为如下:create table tb(dt datetime,val decimal(18,2))
insert into tb values('2011-1-1 0:10:01', 0.1)
insert into tb values('2011-1-1 0:20:01', 0.5)
insert into tb values('2011-1-1 0:30:01', 1.1)
insert into tb values('2011-1-1 0:40:01', 1.3)
insert into tb values('2011-1-1 0:50:01', 1.5)
insert into tb values('2011-1-1 1:00:01', 1.7)
insert into tb values('2011-1-1 1:10:01', 0.8)
insert into tb values('2011-1-1 1:20:01', 1.6)
insert into tb values('2011-1-1 1:30:01', 0.8)
goselect min(dt) 开始时间, max(dt) 结束时间 from
(
select m.* , m.px - ((select count(1) from 
(
  select t.* ,(select count(1) from tb where dt < t.dt) + 1 px from tb t
) n where n.val > 1 and n.px < m.px) + 1) px2 from
(
  select t.* ,(select count(1) from tb where dt < t.dt) + 1 px from tb t
) m where m.val > 1) k
group by px2 having count(1) >= 3drop table tb/*
开始时间                                                   结束时间                                                   
------------------------------------------------------ ------------------------------------------------------ 
2011-01-01 00:30:01.000                                2011-01-01 01:00:01.000（所影响的行数为 1 行）
*/

Answer 4

我上面的判断是根据时间大小.如果你需要每隔十分钟,把dt < t.dt更改为datediff(mm,dt,t.dt)= 10

Answer 5

create table tb(dt datetime,val decimal(18,2))
insert into tb values('2011-1-1 0:10:01', 0.1)
insert into tb values('2011-1-1 0:20:01', 0.5)
insert into tb values('2011-1-1 0:30:01', 1.1)
insert into tb values('2011-1-1 0:40:01', 1.3)
insert into tb values('2011-1-1 0:50:01', 1.5)
insert into tb values('2011-1-1 1:00:01', 1.7)
insert into tb values('2011-1-1 1:10:01', 0.8)
insert into tb values('2011-1-1 1:20:01', 1.6)
insert into tb values('2011-1-1 1:30:01', 0.8)
goselect t1.dt , t1.val from tb t1,
(
select min(dt) 开始时间, max(dt) 结束时间 from
(
select m.* , m.px - ((select count(1) from 
(
  select t.* ,(select count(1) from tb where dt < t.dt) + 1 px from tb t
) n where n.val > 1 and n.px < m.px) + 1) px2 from
(
  select t.* ,(select count(1) from tb where dt < t.dt) + 1 px from tb t
) m where m.val > 1) k
group by px2 having count(1) >= 3
) t2
where t1.dt between t2.开始时间 and t2.结束时间
drop table tb/*
dt                                                     val                  
------------------------------------------------------ -------------------- 
2011-01-01 00:30:01.000                                1.10
2011-01-01 00:40:01.000                                1.30
2011-01-01 00:50:01.000                                1.50
2011-01-01 01:00:01.000                                1.70（所影响的行数为 4 行）
*/

Answer 6

如果你是大于三次,把having count(1) >= 3 改为 > 3create table tb(dt datetime,val decimal(18,2))
insert into tb values('2011-1-1 0:10:01', 0.1)
insert into tb values('2011-1-1 0:20:01', 0.5)
insert into tb values('2011-1-1 0:30:01', 1.1)
insert into tb values('2011-1-1 0:40:01', 1.3)
insert into tb values('2011-1-1 0:50:01', 1.5)
insert into tb values('2011-1-1 1:00:01', 1.7)
insert into tb values('2011-1-1 1:10:01', 0.8)
insert into tb values('2011-1-1 1:20:01', 1.6)
insert into tb values('2011-1-1 1:30:01', 0.8)
goselect t1.dt , t1.val from tb t1,
(
select min(dt) 开始时间, max(dt) 结束时间 from
(
select m.* , m.px - ((select count(1) from 
(
  select t.* ,(select count(1) from tb where dt < t.dt) + 1 px from tb t
) n where n.val > 1 and n.px < m.px) + 1) px2 from
(
  select t.* ,(select count(1) from tb where dt < t.dt) + 1 px from tb t
) m where m.val > 1) k
group by px2 having count(1) > 3
) t2
where t1.dt between t2.开始时间 and t2.结束时间
drop table tb/*
dt                                                     val                  
------------------------------------------------------ -------------------- 
2011-01-01 00:30:01.000                                1.10
2011-01-01 00:40:01.000                                1.30
2011-01-01 00:50:01.000                                1.50
2011-01-01 01:00:01.000                                1.70（所影响的行数为 4 行）
*/

Answer 7

select  
*,
case
when (select case when count(1)=3 then 1 else 0 end from (select top 3 * from tb where 时间<=a.时间 order by 时间 desc) S where val>1)=1
     or (select case when count(1)=3 then 1 else 0 end from (select top 3 * from tb where 时间>=a.时间 order by 时间 ) S where val>1)=1
     then 1
else 0
end
是否连续
from tb A

Answer 8

create table tb(dt datetime,val decimal(18,2))
insert into tb values('2011-1-1 0:10:01', 0.1)
insert into tb values('2011-1-1 0:20:01', 0.5)
insert into tb values('2011-1-1 0:30:01', 1.1)
insert into tb values('2011-1-1 0:40:01', 1.3)
insert into tb values('2011-1-1 0:50:01', 1.5)
insert into tb values('2011-1-1 1:00:01', 1.7)
insert into tb values('2011-1-1 1:10:01', 0.8)
insert into tb values('2011-1-1 1:20:01', 1.6)
insert into tb values('2011-1-1 1:30:01', 0.8)select   
*,
case
when (select case when count(1)=3 then 1 else 0 end from (select top 3 * from tb where dt<=a.dt order by dt desc) S where val>1)=1
  or (select case when count(1)=3 then 1 else 0 end from (select top 3 * from tb where dt>=a.dt order by dt ) S where val>1)=1
  then 1
else 0
end
是否连续
from tb Adt                      val     是否连续
----------------------- ------- -
2011-01-01 00:10:01.000 0.10 0
2011-01-01 00:20:01.000 0.50 0
2011-01-01 00:30:01.000 1.10 1
2011-01-01 00:40:01.000 1.30 1
2011-01-01 00:50:01.000 1.50 1
2011-01-01 01:00:01.000 1.70 1
2011-01-01 01:10:01.000 0.80 0
2011-01-01 01:20:01.000 1.60 0
2011-01-01 01:30:01.000 0.80 0