select t1.cid,t1.pv,t1.rk
from (select p.cid,
round(count(*)*1.0,3) pv,--转为浮点数并保留三位小数
row_number()over(order by count(*) desc,c.release_date desc) rk对重复次数总和降序排名 对发布日期降序排名
from course_info_tb c
join play_record_tb p on c.cid=p.cid
group by p.cid,p.uid,c.release_date) t1--对用户 课程和发布时间分组
where t1.pv>1 and t1.rk<=3
order by rk