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

tao85322 2009-04-10 12:59:12
我现在有个表中存有从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 |
+---------------------+--------------+--------+----------+---------+
如何获得每小时呢??
...全文
123 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
Zoezs 2009-04-10
  • 打赏
  • 举报
回复

select '20'+convert(char(6),tr_time,12)+datepart(hh,tr_time)
2009041013
你把时间搞成这种格式的,根据这种格式分组。
liangCK 2009-04-10
  • 打赏
  • 举报
回复
石头,人家的好像是MySQL
htl258_Tony 2009-04-10
  • 打赏
  • 举报
回复
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
  • 打赏
  • 举报
回复
看错了。还是1楼的吧
-狙击手- 2009-04-10
  • 打赏
  • 举报
回复
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
  • 打赏
  • 举报
回复
把你的结果放在临时表中,
select * from #tb t where not exists(select 1 from #tb where id=t.id and endtime<t.endtime)
-狙击手- 2009-04-10
  • 打赏
  • 举报
回复
convert(char(13),endtime,120)

22,181

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧