SELECT
b.hours AS hours,
IFNULL( a.avgNumber, 0 ) AS avgNumber
FROM
(
SELECT
DATE_FORMAT( HOUR, '%H' ) + 1-1 hours
FROM
( SELECT @cdate := DATE_ADD( @cdate, INTERVAL - 1 HOUR ) HOUR FROM t_platform_log LIMIT 0, 24 ) f
ORDER BY
hours
) b
LEFT JOIN (
SELECT
DATE_FORMAT( f_create_time, '%H' ) + 1-1 hours,
ROUND( count( * ) / ( timestampdiff( DAY, '2019-10-27', '2019-11-05' ) + 1 ), 2 ) avgNumber
FROM
t_platform_log
WHERE
1 = 1
AND f_create_time >= '2019-10-27 00:00:00'
AND f_create_time <= '2019-11-05 23:59:59'
GROUP BY
DATE_FORMAT( f_create_time, '%H' )
) a ON a.hours = b.hours
navicat中执行的结果:
java程序中执行的结果:
java代码: