【2020090401】排名 rank over的用法

（1）rank over（order by 列名）------该函数只适用于oracle

例：

select sid, sname,  rank() over (order by score) as srank from sc;

（2）rank over（partition by 列名，order by 列名）

例：

select name, age, score, rank() over (partition by age order by score decs) as rank_num from student;

（按年龄分组，组内按分数降序排名）

 

注：dense_rank() 连续排名

　　rank () 可不连续排名

-- 查询各科成绩前三名的记录

SELECT cid, sid, score, srank FROM
    (SELECT *, dense_rank() over (PARTITION by cid ORDER BY score DESC) AS srank
    FROM sc)
    AS t
WHERE t.srank<=3;