step1:按room_id相等的条件将user_view_tb左连接room_info_tb;

step2:限定时间在23:00到24：00之间；

step3:按room_id分组查找各直播间的在线人数；

step4:按各直播间的在线人数降序排序。

SELECT

a.room_id, room_name, count(distinct user_id) as user_count

from user_view_tb a

left join

room_info_tb b

on a.room_id=b.room_id

where

(in_time<='23:00:00' and out_time>='23:00:00')

OR(in_time BETWEEN '23:00:00' and '24:00:00')

group by a.room_id order by user_count desc;