select h1.room_id,h2.room_name,h1.user_count
from
(select room_id,count(distinct user_id) as user_count
from user_view_tb
where substr(in_time,1,2) in ('23','24') 
or substr(out_time,1,2) in ('23','24')
group by room_id
) as h1
left outer join
(
select room_id,room_name
from room_info_tb
)as h2 on h1.room_id=h2.room_id
order by h1.user_count desc