题解 | 短视频直播间晚上11-12点之间各直播间的在线人数

SELECT
    rt.room_id,
    rt.room_name,
    COUNT(DISTINCT user_id) user_count
FROM user_view_tb ut
JOIN room_info_tb rt ON ut.room_id = rt.room_id
WHERE (SUBSTRING(ut.in_time,1,2) = 23 OR SUBSTRING(ut.out_time,1,2) = 23) OR 
    (SUBSTRING(ut.in_time,1,2) = 00 OR SUBSTRING(ut.out_time,1,2) = 00)
GROUP BY rt.room_id, rt.room_name 
ORDER BY user_count DESC