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

SELECT room_id, room_name, count(room_id) as user_count
FROM(
SELECT ut.room_id as room_id, ut.user_id as user_id, rt.room_name as room_name
FROM user_view_tb as ut
INNER JOIN room_info_tb as rt
ON ut.room_id = rt.room_id
WHERE (CASE
        WHEN HOUR(ut.in_time)>=23 and HOUR(ut.out_time)<=24
        THEN 1
        WHEN HOUR(ut.in_time)<23 and HOUR(ut.out_time) between 23 and 24
        THEN 1
        WHEN HOUR(ut.in_time) between 23 and 24 and HOUR(ut.out_time)>24
        THEN 1
        ELSE NULL
      END)
GROUP BY ut.room_id, rt.room_name, ut.user_id    
) as re
GROUP BY room_id, room_name
ORDER BY count(room_id) DESC, room_id