如何在mysql中按小时总结

Question

例如，我有一个table1 (这只是table1的一部分，table1实际上有57000条记录，起始时间范围从2014-04-08 23:59:56到2014-04-09 23:59:56 )：

+---------------------+
| StartTime           |
+---------------------+
| 2014-04-08 23:59:56 |
| 2014-04-08 23:59:59 |
| 2014-04-09 00:00:03 |
| 2014-04-09 00:00:03 |
| 2014-04-09 00:00:05 |
| 2014-04-09 00:00:08 |
| 2014-04-09 00:00:09 |
| 2014-04-09 00:00:11 |
| 2014-04-09 00:00:11 |
| 2014-04-09 00:00:17 |
+---------------------+

这个表是关于网络流的，启动时间是指每个流的开始时间，有些网络流有相同的启动时间。我想计算一下每小时有多少流量。伪码就像

select count(starttime) as totalPerHour from table1 group by hour; 
(just pseudo-code)
The result should be:
  TimestampHOUR       totalPerHour
  2014-04-09 01:00       46
  2014-04-09 02:00       3
  2014-04-09 03:00       55
  2014-04-09 04:00       12
  2014-04-09 05:00       2369
  2014-04-09 06:00       99

Answer 1

合并小时(.)和伯爵(.)

select hour(StartTime), count(StartTime)
from table1
group by hour(StartTime)

Answer 2

MySQL有hour(..)。用它就像：

select count(*) as totalPerHour from table1 group by HOUR(StartTime);