select r.room_id as room_id,
    r.room_name as room_name,
    count(distinct u.user_id) as user_count #计算呆在直播间的人数，通过user_id计算
from room_info_tb as r 
    left join user_view_tb as u on r.room_id = u.room_id
#进入时间在23-24点或者退出时间在23-24点
where  (u.in_time BETWEEN '23:00:00' AND '24:00:00') 
    OR 
    (u.out_time BETWEEN '23:00:00' AND '24:00:00')
group by r.room_id, r.room_name
order by user_count desc