如何返回这样的记录集

[anlun]

有一个表
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

[rucypli]

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)

• 打赏
• 举报

回复 赞

[dong_y888]

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) 

• 打赏
• 举报

回复 赞

[ACMAIN_CHM]

select 日期,hour(时间),count(*) from 有一个表 group by 日期,hour(时间)

• 打赏
• 举报

回复 赞