如何获得每天中每小时的数据

[tao85322]

我现在有个表中存有从2009-1-1到现在的记录 每15秒有一条记录 我现在想获得某一天中每个小时的第一条记录
现在我已获得制定日期的一个小时的数据
sql:select a.t as endtime,a.id,a.rx,a.tx ,if_name from (select tr_time as t,hex(box_id) as id,if_name,rx,tx from traffic where tr_time between '2009-01-22 11:00:00' and '2009-01-22 12:05:00' group by tr_time desc,box_id,if_name and if_name='ixp0' ) as a group by a.id
结果：
| endtime | id | rx | tx | if_name |
+---------------------+--------------+--------+----------+---------+
| 2009-01-22 12:04:55 | 0016160368F0 | 7152 | 16315145 | br0 |
| 2009-01-22 12:04:55 | 0016161C0000 | 573809 | 20720710 | br0 |
| 2009-01-22 12:04:55 | 0016161DA720 | 1152 | 6472 | br0 |
| 2009-01-22 12:05:00 | 0016161DA7C0 | 570 | 2348 | br0 |
| 2009-01-22 12:04:55 | 001616213AA0 | 14826 | 57030474 | br0 |
+---------------------+--------------+--------+----------+---------+
如何获得每小时呢？？

[-狙击手-]

convert(char(13),endtime,120)

[让你望见影子的墙]

把你的结果放在临时表中，
select * from #tb t where not exists(select 1 from #tb where id=t.id and endtime<t.endtime)

[-狙击手-]

select a.t as endtime,a.id,a.rx,a.tx ,if_name 
into #tmp
from (select tr_time as t,hex(box_id) as id,if_name,rx,tx 
      from traffic 
      where tr_time between '2009-01-22 11:00:00' and '2009-01-22 12:05:00' 
      group by tr_time desc,box_id,if_name and if_name='ixp0' 
     ) as a 
group by a.id 


select *
from #tmp a
where not exists(select 1 from #tmp and convert(char(13),endtime,120) = convert(char(13),a.endtime,120) and endtime < a.endtime)

drop table #tmp

[让你望见影子的墙]

看错了。还是1楼的吧

[htl258_Tony]

select a.t as endtime,a.id,a.rx,a.tx ,if_name from (select tr_time as t,hex(box_id) as id,if_name,rx,tx from traffic where tr_time between '2009-01-22 11:00:00' and '2009-01-22 12:05:00' group by tr_time desc,box_id,if_name and if_name='ixp0' ) as a group by a.id 
having datepart(mi,a.t)%60=0 and datepart(ss,a.t)%60=0

[liangCK]

石头,人家的好像是MySQL

[Zoezs]

select '20'+convert(char(6),tr_time,12)+datepart(hh,tr_time)
2009041013
你把时间搞成这种格式的，根据这种格式分组。