时间间隔查询

ysoni 2011-02-17 04:18:48
时间 温度
2011.02.17 13:47:50 23
2011.02.17 13:47:55 23
2011.02.17 13:48:00 22
2011.02.17 13:48:05 22
2011.02.17 13:48:10 22
2011.02.17 13:48:15 22
2011.02.17 13:48:20 22
2011.02.17 13:53:46 22
2011.02.17 13:53:51 22
2011.02.17 13:53:56 22
2011.02.17 13:54:01 23
2011.02.17 13:54:06 23
2011.02.17 13:54:11 22
2011.02.17 13:54:16 22
2011.02.17 13:54:21 22
2011.02.17 13:54:26 23
2011.02.17 13:54:31 22
2011.02.17 13:54:36 23
2011.02.17 13:54:41 22
2011.02.17 13:54:46 22
2011.02.17 13:54:51 22
2011.02.17 13:54:56 23
2011.02.17 13:55:01 22
2011.02.17 13:55:06 22
2011.02.17 13:55:11 22
2011.02.17 13:55:16 23
2011.02.17 13:55:21 22
2011.02.17 13:55:26 22
2011.02.17 13:55:31 22
2011.02.17 13:55:36 22
2011.02.17 13:55:41 22
2011.02.17 13:55:46 22
2011.02.17 13:55:51 22
2011.02.17 13:55:56 23
2011.02.17 13:56:01 22
2011.02.17 13:56:06 22
2011.02.17 13:56:11 23
2011.02.17 13:56:16 23
2011.02.17 13:56:21 22
2011.02.17 13:56:26 22
2011.02.17 13:56:31 23
2011.02.17 13:56:36 22
2011.02.17 16:07:59 23
2011.02.17 16:08:04 23
2011.02.17 16:08:09 23
2011.02.17 16:08:14 23
2011.02.17 16:08:19 22
2011.02.17 16:08:24 23
2011.02.17 16:08:29 23
2011.02.17 16:08:34 23
2011.02.17 16:08:39 23
2011.02.17 16:08:44 22
2011.02.17 16:08:49 23
2011.02.17 16:08:54 23
2011.02.17 16:08:59 23
2011.02.17 16:09:04 23
2011.02.17 16:09:09 23
2011.02.17 16:09:14 23
2011.02.17 16:09:19 23
2011.02.17 16:09:24 22
2011.02.17 16:09:29 23
2011.02.17 16:09:34 23
2011.02.17 16:09:39 23
要求查询结果为:从起始时间2011.02.17 13:00:00开始,每间隔大于或等于1分钟的记录
2011.02.17 13:47:50 23 <-此记录是与'2011.02.17 13:00:00'相比不小于1分钟

2011.02.17 13:53:46 22 <-此记录是与'2011.02.17 13:47:50'相比不小于1分钟
2011.02.17 13:54:46 22 <-此记录是与'2011.02.17 13:53:46相比不小于1分钟
2011.02.17 13:55:46 22 <-此记录是与'2011.02.17 13:54:46相比不小于1分钟
2011.02.17 16:07:59 23 <-此记录是与'2011.02.17 13:55:46相比不小于1分钟
2011.02.17 16:08:59 23 <-此记录是与'2011.02.17 16:07:09相比不小于1分钟
...全文
208 点赞 收藏 15
写回复
15 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
打一壶酱油 2011-02-18
.......看来最近是一黑到底啊
回复
ysoni 2011-02-18
呵呵,天上掉馅瓶了
[Quote=引用 13 楼 coleling 的回复:]
受之有愧啊
[/Quote]
回复
coleling 2011-02-18
受之有愧啊
回复
ysoni 2011-02-18
abcjun188:不好意思。我结贴时填错了,分给到了11楼。  现在要怎样给你分呢?
回复
coleling 2011-02-18
[Quote=引用 10 楼 abcjun188 的回复:]

SQL code
create table #test(
thetime datetime,
temprature int,
dis float
)

insert #test(thetime,temprature) select '2011.02.17 13:47:50', 23
union all select '2011.02.17 13:47:55', 23
union all sel……
[/Quote]

PFPF,真是相当的帅啊。

顺便说一句,#8是错误的
回复
打一壶酱油 2011-02-17
create table #test(
thetime datetime,
temprature int,
dis float
)

insert #test(thetime,temprature) select '2011.02.17 13:47:50', 23
union all select '2011.02.17 13:47:55', 23
union all select '2011.02.17 13:48:00', 22
union all select '2011.02.17 13:48:05', 22
union all select '2011.02.17 13:48:10', 22
union all select '2011.02.17 13:48:15', 22
union all select '2011.02.17 13:48:20', 22
union all select '2011.02.17 13:53:46' ,22
union all select '2011.02.17 13:53:51' ,22
union all select '2011.02.17 13:53:56' ,22
union all select '2011.02.17 13:54:01', 23
union all select '2011.02.17 13:54:06', 23
union all select '2011.02.17 13:54:11', 22
union all select '2011.02.17 13:54:16', 22
union all select '2011.02.17 13:54:21' ,22
union all select '2011.02.17 13:54:26', 23
union all select '2011.02.17 13:54:31', 22
union all select '2011.02.17 13:54:36', 23
union all select '2011.02.17 13:54:41', 22
union all select '2011.02.17 13:54:46', 22
union all select '2011.02.17 13:54:51' ,22
union all select '2011.02.17 13:54:56', 23
union all select '2011.02.17 13:55:01', 22
union all select '2011.02.17 13:55:06' ,22
union all select '2011.02.17 13:55:11' ,22



declare @distinct float
declare @thetime datetime
set @thetime = '2011.02.17 13:47:50'
set @distinct = 0

update #test set
@distinct = dis = (datediff(ss,@thetime,thetime) + 0.0) /60,
@thetime = (case when @distinct >= 1 then thetime else @thetime end)


select * from #test where dis = 0 or dis >=1
drop table #test

/*
2011-02-17 13:47:50.000 23 0
2011-02-17 13:53:46.000 22 5.933333
2011-02-17 13:54:46.000 22 1
*/
回复
高山 2011-02-17



[Quote=引用 8 楼 ssp2009 的回复:]
SQL code


create table tb (date datetime)

insert into tb
select '2011-02-17 13:47:50' union all
select '2011-02-17 13:47:55' union all
select '2011-02-17 13:48:00' union all
select '2011-02……
[/Quote]
回复
快溜 2011-02-17


create table tb (date datetime)

insert into tb
select '2011-02-17 13:47:50' union all
select '2011-02-17 13:47:55' union all
select '2011-02-17 13:48:00' union all
select '2011-02-17 13:49:10' union all
select '2011-02-17 13:49:50' union all
select '2011-02-17 13:51:10' union all
select '2011-02-17 13:51:50' union all
select '2011-02-17 13:52:10' union all
select '2011-02-17 13:54:50' union all
select '2011-02-17 13:54:55'

select * from tb a
where datediff(mi,a.date,(select top 1 date from tb where date>a.date))>=1

/*
date
-----------------------
2011-02-17 13:47:55.000
2011-02-17 13:48:00.000
2011-02-17 13:49:50.000
2011-02-17 13:51:50.000
2011-02-17 13:52:10.000
回复
高山 2011-02-17
我用了两个游标,楼主看看可以不?
这个还可以优化一下.



declare @thistemp datetime
declare @nexttemp datetime
declare #cc1 cursor for
select td from table3
declare #cc2 cursor for
select td from table3
open #cc1
open #cc2
fetch next from #cc2 into @nexttemp
fetch next from #cc1 into @thistemp
fetch next from #cc2 into @nexttemp -- more times
while @@fetch_status=0
begin
if(dateadd(minute,1,@thistemp) < @nexttemp)
begin
--具体操作
end
--print '+' + cast(@nexttemp as varchar(20));
fetch next from #cc2 into @nexttemp
fetch next from #cc1 into @thistemp
end
close #cc1
close #cc2
deallocate #cc1
deallocate #cc2
/*
Feb 17 2011 4:45PM
#
Feb 17 2011 5:18PM
Feb 17 2011 5:17PM
#
Feb 17 2011 5:20PM
Feb 17 2011 5:19PM
#
Feb 17 2011 5:27PM
*/

select * from table3



select * from table3

create table table3(
tid int primary key identity(1,1),
td datetime
)

insert into table3(td) values(getdate())

tid td
1 2011-02-17 16:45:36.543
2 2011-02-17 16:45:37.043
3 2011-02-17 16:45:37.200
4 2011-02-17 16:45:37.357
5 2011-02-17 16:45:37.543
6 2011-02-17 16:45:37.700
7 2011-02-17 16:45:37.887
8 2011-02-17 16:45:38.077
9 2011-02-17 16:46:01.293
10 2011-02-17 17:18:54.000
11 2011-02-17 17:20:12.310
12 2011-02-17 17:27:41.047

回复
ysoni 2011-02-17
是监控系统数据库,数据是少不了的
回复
快溜 2011-02-17
用临时表可以替代row_number(),问题是不知道你数据多少,如果太多,就算了。
回复
ysoni 2011-02-17
忘了说是SQL 2000,row_number()不能用
回复
快溜 2011-02-17

--纯属虚构,可供参考,不适用。
with cte as
(
select row_number() over(order by 时间) as no,* from tb
)

select * from cte a
where exsits (select 1 from cte a where no=a.no+1 and
datediff(mi,a.时间,时间)>=1)
回复
Jinny09 2011-02-17
使用循環就可以處理此問題
回复
Xiao_Ai_Mei 2011-02-17
这个用游标好处理
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2011-02-17 04:18
社区公告
暂无公告