MYSQL按时间段(按小时分组)分组查询当天小时内数据

一、单表

SELECT
DATE_FORMAT( create_time, '%Y-%m-%d %H' ) as time,
COUNT( DISTINCT member_id) as count
FROM
tmp_wp_table 
GROUP BY
time
ORDER BY
time

结果：

二、多表

多表查询就应该用到union连接， 这里根据需求选择 union 还是 union all 注意union 和 union all 的区别

select a.time,ifnull(b.count,0) as count
from (
    SELECT '01时' time union all
    SELECT '02时' time union all
    SELECT '03时' time union all
    SELECT '04时' time union all
    SELECT '05时' time union all
    SELECT '06时' time union all
    SELECT '07时' time union all
    SELECT '08时' time union all
    SELECT '09时' time union all
    SELECT '10时' time union all
    SELECT '11时' time union all
    SELECT '12时' time union all
    SELECT '13时' time union all
    SELECT '14时' time union all
    SELECT '15时' time union all
    SELECT '16时' time union all
    SELECT '17时' time union all
    SELECT '18时' time union all
    SELECT '19时' time union all
    SELECT '20时' time union all
    SELECT '21时' time union all
    SELECT '22时' time union all
    SELECT '23时' time union all
    SELECT '24时' time
) a left join (
SELECT
DATE_FORMAT(AlarmDate, '%H时' ) as time,
COUNT( DISTINCT ID) as count
  FROM
fencevehicle
  WHERE 
to_days(AlarmDate) = to_days(now())
  GROUP BY time
) b on a.time = b.time ORDER BY a.time

查询结果：