求按小时统计的语句
表: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)
--转自
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2014-12-2 9:37:49 |
赞(
操作
