mysql group by一天24小时,每小时数据都显示出来,没有数据补0实现

参考: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
©️2020 CSDN 皮肤主题: 创作都市 设计师:CSDN官方博客 返回首页