取一小时中的某一条数据解决思路

取一小时中的某一条数据
表中的有一个字段为时间
如下这样一些记录
2012-08-12,13:40:30
2012-08-12,13:41:30
2012-08-12,13:42:30
2012-08-12,13:43:30
2012-08-12,13:44:30
2012-08-12,13:45:30
2012-08-12,14:01:30
2012-08-12,14:02:30
2012-08-12,14:03:30
2012-08-12,14:04:30
2012-08-12,14:05:30
时间为每隔３０秒一条记录
此时我想要做的是，每小时取一条记录，显示出来
比方说，以上数据，最后取得的结果为
2012-08-12,13:__:__中的一条记录
2012-08-12,14:__:__中的一条记录，结果是两条记录

能用什么ＳＱＬ语句写出来吗？

------解决方案--------------------
datediff函数，判断表中的时间跟你上次取得的时间相差1个小时即可。

DATEDIFF(HH,'2012-10-11 12:50','2012-10-11 13:50') = 1
------解决方案--------------------

SQL code


create table tb_text
(datetime1 varchar(50))

insert into tb_text (datetime1)
select '2012-08-12,13:40:30' union all
select '2012-08-12,13:41:30' union all
select '2012-08-12,13:42:30' union all
select '2012-08-12,13:43:30' union all
select '2012-08-12,13:44:30' union all
select '2012-08-12,13:45:30' union all
select '2012-08-12,14:01:30' union all
select '2012-08-12,14:02:30' union all
select '2012-08-12,14:03:30' union all
select '2012-08-12,14:04:30' union all
select '2012-08-12,14:05:30'
/*
（所影响的行数为 11 行）
*/
select * from tb_text
/*
2012-08-12,13:40:30
2012-08-12,13:41:30
2012-08-12,13:42:30
2012-08-12,13:43:30
2012-08-12,13:44:30
2012-08-12,13:45:30
2012-08-12,14:01:30
2012-08-12,14:02:30
2012-08-12,14:03:30
2012-08-12,14:04:30
2012-08-12,14:05:30

*/
update tb_text set datetime1 = replace(datetime1,',',' ')
/*（所影响的行数为 11 行）*/
select * from tb_text
/*

2012-08-12 13:40:30
2012-08-12 13:41:30
2012-08-12 13:42:30
2012-08-12 13:43:30
2012-08-12 13:44:30
2012-08-12 13:45:30
2012-08-12 14:01:30
2012-08-12 14:02:30
2012-08-12 14:03:30
2012-08-12 14:04:30
2012-08-12 14:05:30

*/
alter table tb_text
 alter column datetime1 datetime

select * from tb_text
/*
2012-08-12 13:40:30.000
2012-08-12 13:41:30.000
2012-08-12 13:42:30.000
2012-08-12 13:43:30.000
2012-08-12 13:44:30.000
2012-08-12 13:45:30.000
2012-08-12 14:01:30.000
2012-08-12 14:02:30.000
2012-08-12 14:03:30.000
2012-08-12 14:04:30.000
2012-08-12 14:05:30.000
*/

select max(datetime1) from tb_text group by convert(char(13),datetime1,121)

/*
2012-08-12 13:45:30.000
2012-08-12 14:05:30.000
*/

取一小时中的某一条数据解决思路

相关推荐