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

竟然无需使用time函数