select r.room_id, room_name, user_count from room_info_tb as r
join 
(select room_id,count(1) as user_count from
(
select user_id,room_id from
(
select user_id,room_id,in_time,out_time from user_view_tb
where in_time >= '23:00:00' or ((out_time between '23:00:00' and '24:00:00') or out_time = '00:00:00')) as u
group by user_id,room_id) as t1
group by room_id) as t2
on r.room_id = t2.room_id
order by user_count desc