表格如下id temp time 1 37 9:01
1 36 9:02
1 37 9:03
1 37 9:03
1 37 9:04
1 37 9:05
1 37 9:06
1 35 9:07
1 37 9:08
1 37 9:09
.. .. ...
1 37 10:01
1 35 10:02
1 37 10:03
1 36 10:04
1 37 10:05
1 37 10:06
... ... ... 2 37 9:01
2 37 9:01
2 36 9:02
2 35 9:03
2 37 9:03
2 37 9:04
2 37 9:05
2 37 9:06
2 37 9:07
2 37 9:08
2 37 9:09
.. .. ...
2 37 10:01
2 35 10:02
2 37 10:03
2 36 10:04
2 37 10:05
2 37 10:06
...... ....
3 37 9:01
绝大多数temp是37 ,希望找到这样的记录:在第一次出现 小于37 等于37 小于37 这样的模式之前,没有出现2个连续 小于35 的记录。结果 1 35 10:02
1 36 9:02
1 37 9:03
1 37 9:03
1 37 9:04
1 37 9:05
1 37 9:06
1 35 9:07
1 37 9:08
1 37 9:09
.. .. ...
1 37 10:01
1 35 10:02
1 37 10:03
1 36 10:04
1 37 10:05
1 37 10:06
... ... ... 2 37 9:01
2 37 9:01
2 36 9:02
2 35 9:03
2 37 9:03
2 37 9:04
2 37 9:05
2 37 9:06
2 37 9:07
2 37 9:08
2 37 9:09
.. .. ...
2 37 10:01
2 35 10:02
2 37 10:03
2 36 10:04
2 37 10:05
2 37 10:06
...... ....
3 37 9:01
绝大多数temp是37 ,希望找到这样的记录:在第一次出现 小于37 等于37 小于37 这样的模式之前,没有出现2个连续 小于35 的记录。结果 1 35 10:02
解决方案 »
- 有关MS存储过程,select top 变量如何写?
- 一个高人告诉我:没有安装ACCESS数据库的任何东西(包括引擎)就可以创建、读、写.mdb文件,是这样吗?
- 关于sql连接问题
- 现在要将B表中一字段的内容弄到A表中的对应的字段中,请问怎么写SQL?
- 高手进来看一下,相对来说比较复杂的SQl 语句!摸索了3--4天没有搞定!请帮小弟一把!不慎感激!
- 主键建立问题 偶像们 帮忙
- 请问关于SQLserver2005的
- 救命呀!创建索引视图问题.在线等,召唤大侠回复!!!!
- 看看我的sql哪里错了
- 试图从mdf文件恢复数据,出错
- Sql2008连接问题
- sqlserver 2005数据库日志过大?
create table tb(id int, temp int, time varchar(10))
insert into tb
select 1,37,'9:01' union all
select 1,37,'9:02' union all
select 1,36,'9:03' union all
select 1,37,'9:04' union all
select 1,35,'9:05' union all
select 1,37,'9:06' union all
select 1,37,'9:07' union all
select 1,35,'9:08' union all
select 2,36,'9:09' union all
select 2,37,'9:01' union all
select 2,37,'9:02' union all
select 2,36,'9:03' union all
select 2,37,'9:04' union all
select 2,35,'9:05' union all
select 2,37,'9:06' union all
select 2,37,'9:07' union all
select 2,35,'9:08' union all
select 2,36,'9:09' with cte as
(
select row_number() over(order by getdate()) as no,* from tb
)
select top 1 id,temp,time from cte
where temp < 37 and no not in (select top 2 no from cte where temp<37) drop table tb/*id temp time
----------- ----------- ----------
1 35 9:08
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int, temp int, time datetime)
insert into #
select 1, 37, '9:01' union all
select 1, 36, '9:02' union all
select 1, 37, '9:03' union all
select 1, 37, '9:03' union all
select 1, 37, '9:04' union all
select 1, 37, '9:05' union all
select 1, 37, '9:06' union all
select 1, 35, '9:07' union all
select 1, 37, '9:08' union all
select 1, 37, '9:09' union all
select 1, 37, '10:01' union all
select 1, 35, '10:02' union all
select 1, 37, '10:03' union all
select 1, 36, '10:04' union all
select 1, 37, '10:05' union all
select 1, 37, '10:06' union all
select 2, 37, '9:01' union all
select 2, 37, '9:01' union all
select 2, 36, '9:02' union all
select 2, 35, '9:03' union all
select 2, 37, '9:03' union all
select 2, 37, '9:04' union all
select 2, 37, '9:05' union all
select 2, 37, '9:06' union all
select 2, 37, '9:07' union all
select 2, 37, '9:08' union all
select 2, 37, '9:09' union all
select 2, 37, '10:01' union all
select 2, 35, '10:02' union all
select 2, 37, '10:03' union all
select 2, 36, '10:04' union all
select 2, 37, '10:05' union all
select 2, 37, '10:06' union all
select 3, 37, '9:01';with cte as
(
select row=row_number()over(order by id,time),* from #
)
select top 1 * from cte t where temp < 37
and exists (select 1 from cte where row=t.row+1 and temp=37)
and exists (select 1 from cte where row=t.row+2 and temp<37)
and exists (select 1 from cte where row between t.row-2 and t.row-1 and temp>=37)
order by row
/*
row id temp time
-------------------- ----------- ----------- -----------------------
12 1 35 1900-01-01 10:02:00.000
*/
select top 1 * from #cte t where temp < 37
and exists (select 1 from #cte where row=t.row+1 and temp=37)
and exists (select 1 from #cte where row=t.row+2 and temp<37)
and exists (select 1 from #cte where row between t.row-2 and t.row-1 and temp>=37)
order by row
/*
row id temp time
-------------------- ----------- ----------- -----------------------
12 1 35 1900-01-01 10:02:00.000
*/
and exists (select 1 from #cte where id=t.id and row=t.row+1 and temp=37)
and exists (select 1 from #cte where id=t.id and row=t.row+2 and temp<37)
and not exists
(
select 1 from #cte x where x.id=t.id and x.row<t.row and x.temp<37 and
exists(select 1 from #cte where id=x.id and row in (x.row-1,x.row+1) and temp<37)
)