mysql - SQL查询将结果分组到每小时的存储桶中

我有一个非常简单的MySQL表

Name  |  Time  |  Count
James |  11:00 |  10
Simon |  11:00 |  5
James |  11:30 |  4
Oliver|  11:30 |  2
James |  12:00 |  1
etc.

我想创建一个MySQL查询，该查询对每个人的Count求和，并输出最近1小时的值，最近3小时的值，最近6小时的值，最近12小时的值和最近24小时的值。

例如如果现在是12:59，则上表将输出：

Name  |  Last-1  |  Last-3  | Last-6
James |  1       |  15      | 15
Simon |  0       |  5       | 5
Oliver|  0       |  2       | 2

是否可以在单个查询中执行此操作？到目前为止，我有：

SELECT name, HOUR( NOW( ) ) - HOUR( time ) AS lasthours, SUM( count ) AS c
FROM table
GROUP BY name, lasthours
HAVING lasthours =0
ORDER BY c DESC

这给了我一个小时的时间，但是我如何获得更多的列？

任何帮助，不胜感激。

编辑：

接受的答案需要稍作调整，结果如下：

SELECT  name ,
        SUM( IF( HOUR( TIMEDIFF( NOW( ) ,  time ) ) <=0,  count , 0 ) ) AS  `Last hour` ,
        SUM( IF( HOUR( TIMEDIFF( NOW( ) ,  time ) ) <=3,  count , 0 ) ) AS  `Last 3 hours` ,
        SUM( IF( HOUR( TIMEDIFF( NOW( ) ,  time ) ) <=6,  count , 0 ) ) AS  `Last 6 hours` ,
        SUM( IF( HOUR( TIMEDIFF( NOW( ) ,  time ) ) <=12,  count , 0 ) ) AS  `Last 12 hours` ,
        SUM( IF( HOUR( TIMEDIFF( NOW( ) ,  time ) ) <=24,  count , 0 ) ) AS  `Last 24 hours` ,
        SUM( IF( HOUR( TIMEDIFF( NOW( ) ,  time ) ) <=48,  count , 0 ) ) AS  `Last 2 days` ,
        SUM( IF( HOUR( TIMEDIFF( NOW( ) ,  time ) ) <=72,  count , 0 ) ) AS  `Last 3 days` ,
        SUM( IF( HOUR( TIMEDIFF( NOW( ) ,  time ) ) <=96,  count , 0 ) ) AS  `Last 4 days` ,
        SUM( IF( HOUR( TIMEDIFF( NOW( ) ,  time ) ) <=120,  count , 0 ) ) AS  `Last 5 days` ,
        SUM( IF( HOUR( TIMEDIFF( NOW( ) ,  time ) ) <=144,  count , 0 ) ) AS  `Last 6 days` ,
        SUM( IF( HOUR( TIMEDIFF( NOW( ) ,  time ) ) <=168,  count , 0 ) ) AS  `Last week`
FROM  table
GROUP BY  name
ORDER BY `Last hour` DESC

最佳答案

SELECT   `name`,
         SUM(IF(HOUR(TIMEDIFF(CURTIME(), `Time`)) <= 1, `Count`, 0)) AS `Last-1`,
         SUM(IF(HOUR(TIMEDIFF(CURTIME(), `Time`)) <= 3, `Count`, 0)) AS `Last-3`,
         SUM(IF(HOUR(TIMEDIFF(CURTIME(), `Time`)) <= 6, `Count`, 0)) AS `Last-6`
FROM     `table`
GROUP BY `name`

在sqlfiddle上查看。

关于mysql - SQL查询将结果分组到每小时的存储桶中，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11261060/