row_number:用途非常广泛,排序最好用它,它会为查询出来的每一行记录生成一个序号,依次排序且不会重复,注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。
rank:用于返回结果集的分区内每行的排名,行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名,与row_number函数不同的是,rank函数考虑到了over子句中排序字段值相同的情况,如果使用rank函数来生成序号,over子句中排序字段值相同的序号是一样的,后面字段值不相同的序号将跳过相同的排名号排下一个,也就是相关行之前的排名数加一,可以理解为根据当前的记录数生成序号,后面的记录依此类推。
例如:1 1 3 3 5 5,可见并不会出现第二名,第四名这样的情况。
#按照课程对学生的成绩进行排序:
select name,course,row_number() over(partition by course order by score desc) rank from student;
dense_rank:函数的功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时,将不跳过相同排名号,rank值紧接上一次的rank值。在各个分组内,rank()是跳跃排序,有两个第一名时接下来就是第三名,dense_rank()是连续排序,有两个第一名时仍然跟着第二名。
例如:1 1 2 2 3 3…第二名仍然会出现
#LeetCode上的一道题目
select score,dense_rank() over(order by score desc) as 'rank' from scores order by score desc;
select s1.score, count(distinct s2.score) 'rank'
from scores s1
inner join
scores s2
on s1.score<=s2.score
group by s1.id
order by s1.score desc;
partition by:用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组
#以a,b字段进行分组,在分组内按照C进行排序
SELECT a,b,c,rank () OVER (PARTITION BY a,b ORDER BY c) rank FROM test