MySQL按时间统计每个小时记录数

方案1：

SELECT  @rownum := @rownum + 1 AS ID,
        CONCAT((CASE WHEN t.hour = 24 THEN 0 ELSE t.hour END),':00:00') AS TIME, COUNT(*) AS COUNT
FROM (SELECT id, HOUR(DATE_FORMAT(createtime,'%H:%i:%s'))+1 AS HOUR FROM baby WHERE DATE_FORMAT(createtime,'%Y-%m-%d')='2016-10-16') t,
     (SELECT @rownum := 0) r
GROUP BY TIME ORDER BY ID
 
//这个计数, 是LESS THAN TIME的计数方法, 即11:xx:xx的记录是记到12:00:00下面的, 而不是11:00:00;

上面的修改

SELECT  @rownum := @rownum + 1 AS ID,
        CONCAT((CASE WHEN t.hour = 24 THEN 0 ELSE t.hour END),'') AS TIME, COUNT(*) AS COUNT
FROM (SELECT id, HOUR(DATE_FORMAT(createtime,'%H:%i:%s')) AS HOUR FROM baby WHERE DATE_FORMAT(createtime,'%Y-%m-%d')='2016-10-16') t,
     (SELECT @rownum := 0) r
GROUP BY TIME ORDER BY ID

方案2

1	`SELECT` `LEFT( createtime, 13 ) ,` `COUNT( * )` `FROM` `baby` `WHERE` `DATE_FORMAT(createtime,'%Y-%m-%d')='2016-10-16'` `GROUP` `BY` `LEFT( createtime, 13 )`

如果当前小时无数据目前无法返回0 ！不知道怎么处理了