select u.room_id,room_name,count(distinct user_id) as user_count
from user_view_tb u
left join room_info_tb r
on u.room_id=r.room_id
where (in_time BETWEEN '23:00:00' and '24:00:00')
or (out_time BETWEEN '23:00:00' and '24:00:00')
group by u.room_id order by user_count desc;