题解 | 被重复观看次数最多的3个视频

SELECT t1.cid,t1.pv,ROW_NUMBER() OVER(ORDER BY t1.pv DESC,t2.release_date DESC)
FROM (
    SELECT 
    b.cid,
    SUM(b.c_cnt) pv
    FROM 
    (
        SELECT cid,uid,COUNT(uid) c_cnt
        FROM play_record_tb
        GROUP BY cid,uid
        HAVING COUNT(uid) > 1
    ) b
    GROUP BY b.cid
) t1
RIGHT JOIN course_info_tb t2
ON t1.cid = t2.cid
LIMIT 3;