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

SELECT a.cid,pv,rk
FROM  (
SELECT p.cid,COUNT(p.uid)*1.000 AS pv,ROW_NUMBER() OVER(ORDER BY COUNT(p.uid) DESC, c.release_date DESC ) AS rk
FROM play_record_tb AS p JOIN   course_info_tb AS c ON p.cid = c.cid
WHERE p.cid in  (
  SELECT p.cid
  FROM play_record_tb AS p JOIN   course_info_tb AS c ON p.cid = c.cid
  GROUP BY p.cid,p.uid
  HAVING COUNT(*)>=2
      )
GROUP BY p.cid,p.uid,c.release_date
HAVING COUNT(*) > 1
) AS a JOIN course_info_tb AS c ON c.cid=a.cId
ORDER BY rk LIMIT 3