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

SELECT 
    t1.cid, 
    pv,
    ROW_NUMBER() OVER(ORDER BY pv DESC, ct.release_date DESC) rk
FROM 
    (SELECT 
        cid,
        CAST(COUNT(*) AS DECIMAL(7,3)) AS pv
    FROM play_record_tb
    GROUP BY uid, cid
    HAVING pv > 1
    ORDER BY pv DESC ) t1
JOIN course_info_tb ct ON ct.cid = t1.cid
ORDER BY rk
LIMIT 3