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

SELECT
    T2.cid,T2.pv,T2.rk
FROM
    (
        SELECT
            t1.cid,
            t1.pv,
            t1.release_date,
            ROW_NUMBER() OVER (
                ORDER BY
                    CAST(t1.pv AS DECIMAL) DESC,
                    t1.release_date DESC
            ) AS RK
        FROM
            (
                SELECT
                    p.cid,
                    concat(ROUND(COUNT(*)),'.000') AS pv,
                    p.uid,
                    c.release_date
                FROM
                    course_info_tb AS c
                    LEFT JOIN play_record_tb AS p USING (cid)
                GROUP BY
                    p.uid,
                    p.cid,
                    c.release_date
                HAVING
                    COUNT(*) >= 2
                    AND p.cid IS NOT NULL
            ) AS t1
    ) AS T2
WHERE
    RK <= 3;