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

WITH t1 AS (
    SELECT
           cid,
           uid,
           count(cid) AS pv
    FROM play_record_tb
    GROUP BY uid, cid
    HAVING pv >= 2
),
t2 AS (
    SELECT t1.cid,
           t1.pv,
           ROW_NUMBER()
               over (ORDER BY t1.pv DESC,
                   release_date DESC) AS rk
    FROM t1
    LEFT JOIN course_info_tb a
    ON t1.cid = a.cid
)SELECT cid,
        CONCAT(pv,'.000') AS pv,
        rk
        FROM t2
WHERE rk <= 3;

# 这道题的关键在于concat拼接零，以及ROW_NUMBER排名，不能有重复排名！