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

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

方案1：

?

1 2 3 4 5 6 7

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;

上面的修改

?

1 2 3 4 5

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 ！ 不知道怎么处理了

转载于:https://www.cnblogs.com/jpfss/p/8759199.html