with T1 as ( select * from user_view_tb u where (u.in_time between '23:00:00' and '23:59:59') or (u.out_time between '23:00:00' and '23:59:59') ) select t.room_id as room_id, r.room_name as room_name, count(distinct t.user_id) as user_count from T1 t left join room_info_tb r on t.room_id=r.room_id group by room_id,room_name;