MySql显示每小时的数据

需求显示统计前八小时用户在线数据

 

select * from(
select IFNULL(tt.c,0),d1.time from (select '2018-4-4 09:00:00' time
union select '2018-4-4 10:00:00' time
union select '2018-4-4 11:00:00' time
union select '2018-4-4 12:00:00' time
union select '2018-4-4 13:00:00' time
union select '2018-4-4 15:00:00' time
union select '2018-4-4 16:00:00' time
union select '2018-4-4 17:00:00' time
) d1

LEFT JOIN

(select IFNULL(count(*),0) c,d.time from user_online uo LEFT JOIN
(select '2018-4-4 09:00:00' time
union select '2018-4-4 10:00:00' time
union select '2018-4-4 11:00:00' time
union select '2018-4-4 12:00:00' time
union select '2018-4-4 13:00:00' time
union select '2018-4-4 15:00:00' time
union select '2018-4-4 16:00:00' time
union select '2018-4-4 17:00:00' time) d on 1=1
where (uo.online_time_start<=d.time and uo.online_time_end>=d.time) or (uo.online_time_start<=d.time and uo.online_time_end is null)
) tt on d1.time=tt.time)a order by a.time desc;