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

SELECT b.cid, pv, ROW_NUMBER() OVER(ORDER BY pv DESC, release_date DESC) AS rk
FROM(SELECT DISTINCT a.cid, ROUND(MAX(rc) OVER(PARTITION BY a.cid)*1.0,3) AS pv
     FROM (SELECT cid, uid, COUNT(id) AS rc
           FROM play_record_tb
           GROUP BY cid, uid
           ) a
    ) b
LEFT JOIN course_info_tb cit ON b.cid = cit.cid
ORDER BY rk
LIMIT 3;

注：

1. 先数播放次数，再保留最大的，再对所有播放次数排序→两层子查询
2. 排序函数可以按照两个字段排序→ROW_NUMBER() OVER(ORDER BY pv DESC, release_date DESC) AS rk
3. 整数型数值如何通过ROUND转换成带小数点的→ROUND(MAX(rc) OVER(PARTITION BY a.cid)*1.0,3) AS pv