求按小时统计的语句
表:issue 字段:
id:1
time:2012-04-01 08:15:00
count:12
。。。。。
按小时统计count总数,结果如下
1 2012-04-01 8:00~9:00 134
2 2012-04-01 9:00~10:00 134
——解决方案——————–
如果结果前面的数字是你要的计数的话，那自己另加一个计数列就是了：
- SQL code
mysql> select * from issue; +----+---------------------+-------+ | id | time | count | +----+---------------------+-------+ | 1 | 2012-04-01 08:15:00 | 12 | | 2 | 2012-04-01 09:15:00 | 12 | | 3 | 2012-04-01 08:25:00 | 12 | | 4 | 2012-04-01 10:15:00 | 12 | | 5 | 2012-04-01 09:15:00 | 12 | +----+---------------------+-------+ 5 rows in set (0.00 sec) mysql> select concat(date_format(time, "%Y-%m-%d %k:00~"), hour(time)+1, ":00") as 'time', sum(`count`) as 'count' -> from issue -> group by left(time, 13); +------------------------+-------+ | time | count | +------------------------+-------+ | 2012-04-01 8:00~9:00 | 24 | | 2012-04-01 9:00~10:00 | 24 | | 2012-04-01 10:00~11:00 | 12 | +------------------------+-------+ 3 rows in set (0.00 sec)
------解决方案--------------------
恩 group by left(time, 13);
------解决方案--------------------
select DATE_FORMAT(time,'%Y-%m-%d %H:00'),sum(count)
from issue
group by DATE_FORMAT(time,'%Y-%m-%d %H:00')
------解决方案--------------------
select Date_format(time,'%Y-%M-%d %H:00'),SUM(COUNT)
FROM issue
group byy DATA_FORMAR(time,'%Y-%m-%d %h:00)欢迎大家阅读《求按小时统计的语句,该怎么处理》,跪求各位点评，by 搞代码
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：求按小时统计的语句,该怎么处理
