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