MySQL按小时分组

MySQL按小时分组

您现有的查询可以简化为：

SELECt   CONCAt(HOUR(created), ':00-', HOUR(created)+1, ':00') AS Hours  ,      COUNT(*) AS `usage`FROM     historyWHERe    created BETWEEN '2012-02-07' AND NOW()GROUP BY HOUR(created)

要显示 每一个 小时，包括那些没有数据，就需要外，其所有您想要的数据小时表连接。您可以使用

UNIOn

以下命令在查询中建立这样的表：

SELECt   CONCAt(Hour, ':00-', Hour+1, ':00') AS Hours  ,      COUNT(created) AS `usage`FROM     history  RIGHT JOIN (        SELECT  0 AS Hour         UNIOn ALL SELECT  1 UNIOn ALL SELECT  2 UNIOn ALL SELECT  3         UNIOn ALL SELECT  4 UNIOn ALL SELECT  5 UNIOn ALL SELECT  6         UNIOn ALL SELECT  7 UNIOn ALL SELECT  8 UNIOn ALL SELECT  9         UNIOn ALL SELECT 10 UNIOn ALL SELECT 11 UNIOn ALL SELECT 12         UNIOn ALL SELECT 13 UNIOn ALL SELECT 14 UNIOn ALL SELECT 15         UNIOn ALL SELECT 16 UNIOn ALL SELECT 17 UNIOn ALL SELECT 18         UNIOn ALL SELECT 19 UNIOn ALL SELECT 20 UNIOn ALL SELECT 21         UNIOn ALL SELECT 22 UNIOn ALL SELECT 23  )      AS AllHours ON HOUR(created) = HourWHERe    created BETWEEN '2012-02-07' AND NOW() OR created IS NULLGROUP BY HourORDER BY Hour

但是，对于没有数据的组的处理实际上是业务逻辑的问题，最好将其放置在数据访问层而不是数据库本身中：实际上，对于您的应用程序，每隔一小时使用零值应该是微不足道的缺席。