select r.room_id,r.room_name,uvt.a as user_count from room_info_tb r join (select room_id,count(distinct case when out_time between '23:00:00' and '24:00:00' then user_id
               else NULL end) as a from user_view_tb
group by room_id) uvt on r.room_id = uvt.room_id
order by user_count desc ;