mysql统计24小时数据

示例1:根据时间点统计个数

       SELECT
	      DATE_FORMAT(create_time,'%Y-%m-%d %H') AS date,
	      COUNT( * ) AS dateCount
        FROM
	      sys_menu
        WHERE
	      date( create_time ) = curdate( )
        GROUP BY
	      DATE_FORMAT(create_time,'%Y-%m-%d %H')

实例2:统计24小时,每个小时没有的补0

SELECT
	t1.HOUR date,
	COUNT( t2.HOUR ) dateCount 
FROM
	(
SELECT
	DATE_FORMAT( @cdate := DATE_ADD( @cdate, INTERVAL - 1 HOUR ), '%y-%m-%d %H' ) HOUR 
FROM
	( SELECT @cdate := DATE_ADD( DATE_FORMAT( NOW( ), '%y-%m-%d %H' ), INTERVAL + 1 HOUR ) FROM sys_menu ) t0 
	LIMIT 24 
	) t1
	LEFT JOIN ( SELECT DATE_FORMAT( create_time, '%y-%m-%d %H' ) HOUR FROM sys_menu WHERE create_time >= ( NOW( ) - INTERVAL 24 HOUR ) ) t2 ON t1.HOUR = t2.HOUR 
GROUP BY
	t1.HOUR 
ORDER BY
	t1.HOUR DESC