参考:mysql group by分组查询统计数据时,在没有数据时如何自动补0
最终我自己实现的sql如下:
SELECT a.hour hour, ifnull(b.count, 0) count FROM (
SELECT 0 hour UNION ALL SELECT 1 hour UNION ALL SELECT 2 hour UNION ALL SELECT 3 hour UNION ALL SELECT 4 hour UNION ALL SELECT 5 hour UNION ALL SELECT 6 hour UNION ALL SELECT 7 hour UNION ALL SELECT 8 hour UNION ALL SELECT 9 hour UNION ALL SELECT 10 hour UNION ALL SELECT 11 hour UNION ALL SELECT 12 hour
UNION ALL SELECT 13 hour UNION ALL SELECT 14 hour UNION ALL SELECT 15 hour UNION ALL SELECT 16 hour UNION ALL SELECT 17 hour UNION ALL SELECT 18 hour UNION ALL SELECT 19 hour UNION ALL SELECT 20 hour UNION ALL SELECT 21 hour UNION ALL SELECT 22 hour UNION ALL SELECT 23 hour
) a LEFT JOIN
(
SELECT
hour(currtime) hour,
count(currtime) count
FROM car_base_state
WHERE electricity_ischarging = 1 AND date_format(currtime, '%Y-%m-%d') = current_date
GROUP BY date_format(currtime, '%Y%m%d-%H'), hour
) b
ON a.hour=b.hour
ORDER BY hour