我想做一个查询,完成下面的功能。
表结构如下:
日期时间 含水率
2009-04-01 10:00:00 2.63
2009-04-01 10:00:01 3.63
2009-04-01 10:00:02 5.63
2009-04-01 10:00:03 0.63
2009-04-01 10:00:04 23.63
2009-04-01 10:00:05 21.63
2009-04-01 10:00:06 22.63
2009-04-01 10:00:07 2.50
2009-04-01 10:00:08 12.63
2009-04-01 10:00:09 2.62
2009-04-01 10:00:10 2.88
2009-04-01 10:00:11 42.63
2009-04-01 10:00:12 2.10
2009-04-01 10:00:13 2.63
... ...
表内的数据每一秒一条记录。
想要查询在2009-04-01 10:00:01到2009-04-01 10:00:24时间内,每隔2秒的数据记录。
举个例子在2009-04-01 10:00:00到2009-04-01 10:00:04这个时间段内有四条记录,每隔2秒的数据就只有两条:02和04秒的那两条。各位高手请帮忙!谢谢!
表结构如下:
日期时间 含水率
2009-04-01 10:00:00 2.63
2009-04-01 10:00:01 3.63
2009-04-01 10:00:02 5.63
2009-04-01 10:00:03 0.63
2009-04-01 10:00:04 23.63
2009-04-01 10:00:05 21.63
2009-04-01 10:00:06 22.63
2009-04-01 10:00:07 2.50
2009-04-01 10:00:08 12.63
2009-04-01 10:00:09 2.62
2009-04-01 10:00:10 2.88
2009-04-01 10:00:11 42.63
2009-04-01 10:00:12 2.10
2009-04-01 10:00:13 2.63
... ...
表内的数据每一秒一条记录。
想要查询在2009-04-01 10:00:01到2009-04-01 10:00:24时间内,每隔2秒的数据记录。
举个例子在2009-04-01 10:00:00到2009-04-01 10:00:04这个时间段内有四条记录,每隔2秒的数据就只有两条:02和04秒的那两条。各位高手请帮忙!谢谢!
解决方案 »
- 请问这两条查询语句,能不能通过拼接写成一条SQL语句?
- 简单的存储过程,看看错在哪里呢?
- 按专业由高到低排序成绩,取各专业内成绩由高到低30%的学生。小弟先感谢了!
- 两个表相关联,我想删除其中的一条记录该怎么办?请各位大哥帮助????
- 简单问题高分求解!“发生错误1069(由于登陆失败而无法启动服务)”!
- 在win2000专业版上应该安装sql server 2000的什么版本???我安装专业版不让我安服务器端只让安客户端
- INFORMIX数据库如何给表加锁?
- 在VFP中如何创建进度条(先谢谢了!!!)
- 请教SQL语言编程问题!!
- 关于海量数据的存储问题,欢迎各位发表意见
- 存储过程中赋值的疑问
- JavaScript连接数据库的问题
insert @t select '2009-04-01 10:00:00',2.63
insert @t select '2009-04-01 10:00:01',3.63
insert @t select '2009-04-01 10:00:02',5.63
insert @t select '2009-04-01 10:00:03',0.63
insert @t select '2009-04-01 10:00:04',23.63
insert @t select '2009-04-01 10:00:05',21.63
insert @t select '2009-04-01 10:00:06',22.63
insert @t select '2009-04-01 10:00:07',2.5
insert @t select '2009-04-01 10:00:08',12.63
insert @t select '2009-04-01 10:00:09',2.62
insert @t select '2009-04-01 10:00:10',2.88
insert @t select '2009-04-01 10:00:11',42.63
insert @t select '2009-04-01 10:00:12',2.1
insert @t select '2009-04-01 10:00:13',2.63select * from @t where datepart(ss,日期时间)%2=0日期时间 含水率
----------------------- ---------------------------------------
2009-04-01 10:00:00.000 2.63
2009-04-01 10:00:02.000 5.63
2009-04-01 10:00:04.000 23.63
2009-04-01 10:00:06.000 22.63
2009-04-01 10:00:08.000 12.63
2009-04-01 10:00:10.000 2.88
2009-04-01 10:00:12.000 2.10(7 行受影响)
==========
秒數是 奇數 就可以了
where datepart(second,getdate())%2 =1
insert into @table(dt,target) select 日期时间,含水率 from 表 where (...) order by 日期时间
select dt 日期时间,target 含水率 from @table where ident_id%2=0 (或ident_id%2=1)
如果開始時間的秒數是 偶,那就用偶數select * from T
where datepart(second, 日期時間)%2 = datepart(second,@begin) %2
and 日期時間 between @begin and @end
declare @t table(日期时间 datetime,含水率 dec(10,2))
insert @t select '2009-04-01 10:00:00',2.63
insert @t select '2009-04-01 10:00:01',3.63
insert @t select '2009-04-01 10:00:02',5.63
insert @t select '2009-04-01 10:00:03',0.63
insert @t select '2009-04-01 10:00:04',23.63
insert @t select '2009-04-01 10:00:05',21.63
insert @t select '2009-04-01 10:00:06',22.63
insert @t select '2009-04-01 10:00:07',2.5
insert @t select '2009-04-01 10:00:08',12.63
insert @t select '2009-04-01 10:00:09',2.62
insert @t select '2009-04-01 10:00:10',2.88
insert @t select '2009-04-01 10:00:11',42.63
insert @t select '2009-04-01 10:00:12',2.1
insert @t select '2009-04-01 10:00:13',2.63select * from @t where 日期时间 between '2009-04-01 10:00:01' and '2009-04-01 10:00:24' and datepart(second,日期时间)%2=0
insert t1 select '2009-04-01 10:00:00',2.63
insert t1 select '2009-04-01 10:00:01',3.63
insert t1 select '2009-04-01 10:00:02',5.63
insert t1 select '2009-04-01 10:00:03',0.63
insert t1 select '2009-04-01 10:00:04',23.63
insert t1 select '2009-04-01 10:00:05',21.63
insert t1 select '2009-04-01 10:00:06',22.63
insert t1 select '2009-04-01 10:00:07',2.5
insert t1 select '2009-04-01 10:00:08',12.63
insert t1 select '2009-04-01 10:00:09',2.62
insert t1 select '2009-04-01 10:00:10',2.88
insert t1 select '2009-04-01 10:00:11',42.63select * from t1 t where datepart(second,日期时间)%2=0日期时间 含水率
----------------------- ---------------------------------------
2009-04-01 10:00:00.000 2.63
2009-04-01 10:00:02.000 5.63
2009-04-01 10:00:04.000 23.63
2009-04-01 10:00:06.000 22.63
2009-04-01 10:00:08.000 12.63
2009-04-01 10:00:10.000 2.88(6 行受影响)
drop proc sp_test
go
create proc sp_test
(
@dis int = 2--Set any value as you are prone.
)
as
begin
declare @t table(日期时间 datetime,含水率 dec(10,2))
insert @t select '2009-04-01 10:00:00',2.63
insert @t select '2009-04-01 10:00:01',3.63
insert @t select '2009-04-01 10:00:02',5.63
insert @t select '2009-04-01 10:00:03',0.63
insert @t select '2009-04-01 10:00:04',23.63
insert @t select '2009-04-01 10:00:05',21.63
insert @t select '2009-04-01 10:00:06',22.63
insert @t select '2009-04-01 10:00:07',2.5
insert @t select '2009-04-01 10:00:08',12.63
insert @t select '2009-04-01 10:00:09',2.62
insert @t select '2009-04-01 10:00:10',2.88
insert @t select '2009-04-01 10:00:11',42.63
insert @t select '2009-04-01 10:00:12',2.1
insert @t select '2009-04-01 10:00:13',2.63 select 日期时间,含水率
from
(
select row_number() over (order by 日期时间 asc) as id, * from @t
) as A
where A.id%@dis = 1 and A.id <> 1
end
go
exec sp_test 5
go
drop proc sp_test
go
datepart(second,getdate())%2 =0 ---每兩秒取一次數據.
datepart(second,getdate())%3 =0 ---每三秒取一次數據.
...
datepart(second,getdate())%n =0 ---每N秒取一次數據.N要小于60, 大於60的話就用分鐘來區分.
服务器: 消息 195,级别 15,状态 10,过程 sp_test,行 26
'row_number' 不是可以识别的 函数名。
declare @t table(日期时间 datetime,含水率 dec(10,2))
insert @t select '2009-04-01 10:00:01',3.63
insert @t select '2009-04-01 10:00:02',5.63
insert @t select '2009-04-01 10:00:03',0.63
insert @t select '2009-04-01 10:00:04',23.63
insert @t select '2009-04-01 10:00:05',21.63
insert @t select '2009-04-01 10:00:06',22.63
insert @t select '2009-04-01 10:00:07',2.5
insert @t select '2009-04-01 10:00:08',12.63
insert @t select '2009-04-01 10:00:09',2.62
insert @t select '2009-04-01 10:00:10',2.88select * from @t where datepart(ss,日期时间)%3=1日期时间 含水率
----------------------- ---------------------------------------
2009-04-01 10:00:01.000 3.63
2009-04-01 10:00:04.000 23.63
2009-04-01 10:00:07.000 2.50
2009-04-01 10:00:10.000 2.88(4 行受影响)想查询某一段,再加个条件就行了。
insert @t select '2009-04-01 10:00:00',2.63
insert @t select '2009-04-01 10:00:01',3.63
insert @t select '2009-04-01 10:00:02',5.63
insert @t select '2009-04-01 10:00:03',0.63
insert @t select '2009-04-01 10:00:04',23.63
insert @t select '2009-04-01 10:00:05',21.63
insert @t select '2009-04-01 10:00:06',22.63
insert @t select '2009-04-01 10:00:07',2.5
insert @t select '2009-04-01 10:00:08',12.63
insert @t select '2009-04-01 10:00:09',2.62
insert @t select '2009-04-01 10:00:10',2.88
insert @t select '2009-04-01 10:00:11',42.63
insert @t select '2009-04-01 10:00:12',2.1
insert @t select '2009-04-01 10:00:13',2.63select * from @t where datepart(ss,日期时间)%3=1 and 日期时间 between '2009-04-01 10:00:01' and '2009-04-01 10:00:10'日期时间 含水率
----------------------- ---------------------------------------
2009-04-01 10:00:01.000 3.63
2009-04-01 10:00:04.000 23.63
2009-04-01 10:00:07.000 2.50
2009-04-01 10:00:10.000 2.88(4 行受影响)这个应该明白了吧
估计楼主是SQL2000的原因,换成
select * from @t where datepart(ss,日期时间)@dis = 1