如何返回这样的记录集

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
...全文
101 3 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
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)

56,867

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

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