题解 | 短视频直播间晚上11-12点之间各直播间的在线人数

with a as(
select room_id,user_id,date_format(in_time,'%H') as intime,date_format(out_time,'%H') as outtime 
from user_view_tb
having intime in (23,24) or outtime in (23,24)) #要把情况考虑全，有可能是从23点进入直播间的，也有可能是之前一直看到23点-24点的

select a.room_id,room_name,count(distinct user_id) as user_count  #要考虑id为4的用户进了两次1003房间，算得是人数，不是人次
from a
join room_info_tb
on a.room_id=room_info_tb.room_id
group by a.room_id,room_name
order by user_count desc