Mysql 统计24小时数据没有补0

Mysql 统计24小时数据没有补0
SELECT
t1.HOUR date,
DATE_FORMAT( t1.HOUR, ‘%H’ ) time,
COUNT( t2.HOUR ) dateCount
FROM
(
##当天时间段
SELECT
DATE_FORMAT(
DATE_SUB( DATE_FORMAT( NOW( ), ‘%Y-%m-%d’ ), INTERVAL ( - ( @i := @i + 1 ) ) HOUR ),
‘%Y-%m-%d %H’
) AS ‘HOUR’
FROM
(
SELECT
a
FROM
( SELECT ‘1’ AS a UNION SELECT ‘2’ UNION SELECT ‘3’ UNION SELECT ‘4’ ) AS a
JOIN ( SELECT ‘1’ UNION SELECT ‘2’ UNION SELECT ‘3’ UNION SELECT ‘4’ UNION SELECT ‘5’ UNION SELECT ‘6’ ) AS b ON 1
) AS b,
( SELECT @i :=- 1 ) AS i
) t1
LEFT JOIN ( SELECT DATE_FORMAT( create_time, ‘%Y-%m-%d %H’ ) HOUR FROM sys_user WHERE create_time >= ( NOW( ) - INTERVAL 24 HOUR ) ) t2 ON t1.HOUR = t2.HOUR
GROUP BY
t1.HOUR
ORDER BY
time ASC;

测试表t_year,主要字段create_time