时间 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 2:10:01 1.5
2011-1-2 1:20:04 1.6
2011-1-2 1:30:01 0.8
2011-1-2 1:40:01 1.2
sql2000
查出连续时间段内(上下条时间不超过11分钟),val超过1并大于3次的语句,希望高手指教
结果查出3,4,5,6条
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 2:10:01 1.5
2011-1-2 1:20:04 1.6
2011-1-2 1:30:01 0.8
2011-1-2 1:40:01 1.2
sql2000
查出连续时间段内(上下条时间不超过11分钟),val超过1并大于3次的语句,希望高手指教
结果查出3,4,5,6条
上次SQL2005 的脚本你不能执行
真没有必要费那么多心思研究查询语句,不值得。
declare @T table (时间 datetime,val numeric(2,1))
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 2:10:01',1.5 union all
select '2011-1-2 1:20:04',1.6 union all
select '2011-1-2 1:30:01',0.8 union all
select '2011-1-2 1:40:01',1.2create table #t(id int identity,时间 datetime,val numeric(2,1),nid int)
insert into #t
select * from (
select a.* from
(
select *,(select count(1) from @T where 时间<=t.时间) as id from @T t
) a
left join
(
select *,(select count(1) from @T where 时间<=t.时间) as id from @T t
)b
on a.id=b.id+1
where a.val>1 and datediff(mi,b.时间,a.时间)<11)
cc
select 时间,val from #t
where nid between
(select min(nid) from #t group by nid-id having(count(1)>2))
and
(select max(nid) from #t group by nid-id having(count(1)>2))
drop table #t
/*
时间 val
----------------------- ---------------------------------------
2011-01-01 00:30:01.000 1.1
2011-01-01 00:40:01.000 1.3
2011-01-01 00:50:01.000 1.5
2011-01-01 01:00:01.000 1.7
*/
create table tb(date datetime,val decimal(12,2))
insert into tb
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 2:10:01', 1.5 union all
select '2011-1-2 1:20:04', 1.6 union all
select '2011-1-2 1:30:01', 0.8 union all
select '2011-1-2 1:40:01', 1.2
goselect id=identity(int,1,1),date,val
into #tb
from tb
where val >= 1
order by dateselect *,(case when datediff(mi,(select date from #tb where id=t.id-1),date)<=11
or datediff(mi,date,(select date from #tb where id=t.id+1))<=11 then 1 else 0 end) as flag
into #tc
from #tb tselect date,val from #tc where flag = 1drop table tb,#tb,#tc/********************************date 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 行受影响)
create table dzyjb
(时间 datetime, val decimal(3,1))insert into dzyjb
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 2:10:01', 1.5 union all
select '2011-1-2 1:20:04', 1.6 union all
select '2011-1-2 1:30:01', 0.8 union all
select '2011-1-2 1:40:01', 1.2
select identity(int,1,1) rn,时间,val
into #t from dzyjbselect t.* from
(select a.时间,a.val
from #t a
left join #t b
on a.rn=b.rn-1
where datediff(mi,a.时间,b.时间)<=11
and (a.val>1 and b.val>1)
union
select b.时间,b.val
from #t a
left join #t b
on a.rn=b.rn-1
where datediff(mi,a.时间,b.时间)<=11
and (a.val>1 and b.val>1)) t时间 val
----------------------- ---------------------------------------
2011-01-01 00:30:01.000 1.1
2011-01-01 00:40:01.000 1.3
2011-01-01 00:50:01.000 1.5
2011-01-01 01:00:01.000 1.7(4 row(s) affected)
真没有必要费那么多心思研究查询语句,不值得。
select * from @T a where exists(select 1 from (select top 1 * from @T x where x.时间<a.时间 order by x.时间 desc) b where a.时间>=dateadd(mi,10,b.时间))
and exists(select 1 from (select top 1 * from @T x where x.时间>a.时间 order by x.时间 asc) b where a.时间<=dateadd(mi,10,b.时间))
and a.val>1;
--那个超过3次没有看懂。一条记录怎么超过3次。