select t1.room_id
,t1.room_name
,count(distinct t.user_id) as user_count
from room_info_tb t1
left join user_view_tb t on t.room_id=t1.room_id
where in_time <= '23:59:59' and out_time >= '23:00:00'
group by t1.room_id
,t1.room_name
order by user_count desc