如何返回这样的记录集

anlun 2013-01-25 06:36:04
有一个表
ID Date(日期) Time(时间) UserName(用户名)

1 2013-1-1 10:00:01 uuu
2 2013-1-1 10:23:23 xxxx
3 2013-1-1 11:20:23 fdf

....

如何返回每小时的记录条数
返回结果按日期时间排序为

Date(日期) Hour(小时) RecordCnt(条数)

2013 -1-1 0-1 23
2013-1-1 1-2 45
2013-1-1 2-3 63
2013-1-1 3-4 22
....
2013-1-1 23-0 34
...

2013-1-24 0-1 12
...全文
69 3 打赏 收藏 举报
写回复
3 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
ACMAIN_CHM 2013-01-26
select 日期,hour(时间),count(*) from 有一个表 group by 日期,hour(时间)
  • 打赏
  • 举报
回复
dong_y888 2013-01-26
select Date,case hour(Time)
when 0 then '0-1' 
when 1 then '1-2'
when 2 then '2-3'
when 3 then '3-4'
when 4 then '4-5'
when 5 then '5-6'
when 6 then '6-7'
when 7 then '7-8'
when 8 then '8-9'
when 9 then '9-10'
when 10 then '10-11'
when 11 then '11-12'
when 12 then '12-13'
when 13 then '13-14'
when 14 then '14-15'
when 15 then '15-16'
when 16 then '16-17'
when 17 then '17-18'
when 18 then '18-19'
when 19 then '19-20'
when 20 then '20-21'
when 21 then '21-22'
when 22 then '22-23'
when 23 then '23-0'
end `Hour` ,count(UserName) RecordCnt from table
group by Date,hour(Time) 
order by Date,hour(Time) 
  • 打赏
  • 举报
回复
rucypli 2013-01-25
select date,(case when time between '00:00:00' and '01:59:59' then '0-1' when time between '01:00:00' and '01:59:59' then '1-2' when time between '02:00:00' and '02:59:59' then '2-3' ... .. when time between '23:00:00' and '23:59:59' then '23-0' end),count(*) from tb group by date,(case when time between '00:00:00' and '01:59:59' then '0-1' when time between '01:00:00' and '01:59:59' then '1-2' when time between '02:00:00' and '02:59:59' then '2-3' ... .. when time between '23:00:00' and '23:59:59' then '23-0' end)
  • 打赏
  • 举报
回复
相关推荐
发帖
MySQL
加入

5.6w+

社区成员

MySQL相关内容讨论专区
社区管理员
  • MySQL
申请成为版主
帖子事件
创建了帖子
2013-01-25 06:36
社区公告
暂无公告