参考自:https://www.cnblogs.com/qiuting/p/7880500.html
https://www.cnblogs.com/YuyuanNo1/p/7929526.html
函数简介:
1、row_number:
用途非常广泛,会对查询出来的每一个记录生成一个记号,依次排序且不会重复。
2、rank:
返回结果集的分区内每行的排名,行的排名是相关行之前的排名加一。简单来说rank函数就是对查询出来的记录进行排名,与row_number函数不同的是,rank函数考虑到了over子句中排序字段值相同的情况,如果使用rank函数来生成序号,over子句中排序字段值相同的序号是一样的,后面字段值不相同的序号将跳过相同的排名号排下一个,也就是相关行之前的排名数加一,可以理解为根据当前的记录数生成序号,后面的记录依此类推。
3、dense_rank:
函数的功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时,将不跳过相同排名号,rank值紧接上一次的rank值。在各个分组内,rank()是跳跃排序,有两个第一名时接下来就是第三名,dense_rank()是连续排序,有两个第一名时仍然跟着第二名。
实例
现在有一张学生表,如下:
select * from student;
ID | NAME | SCORE | COURSE |
5 | elic | 70 | 1 |
4 | dock | 100 | 1 |
3 | clark | 80 | 1 |
2 | bob | 90 | 1 |
1 | alice | 60 | 1 |
10 | jacky | 80 | 2 |
9 | iris | 60 | 2 |
8 | hill | 70 | 2 |
7 | grace | 50 | 2 |
6 | frank | 70 | 2 |
1、按照课程对学生成绩进行排序:
--row_number()顺序排序
select
name
,courese
,row_number() over(partition by course order by score desc) as rank
from student;
--rank() 跳跃排序,如果两个第一,则接下来是第三
select
name
,course
,rank() over(partition by course order by score desc) rank
from student;
--dense_rank() 连续排序,如果两个第一,则接下来是第二
select
name
,course
,dense_rank() over(partition by course order by score desc) rank
from student;
2、取得每门课第一名
--每门课程第一名只取一个:
select * from (select name,course,row_number() over(partition by course order by score desc) rank from student) where rank=1;
--每门课程第一名取所有:
select * from (select name,course,dense_rank() over(partition by course order by score desc) rank from student) where rank=1;
--每门课程第一名取所有:
select * from (select name,course,rank() over(partition by course order by score desc) rank from student) where rank=1;
Partition by关键字介绍
Parttion by关键字是Oracle中分析性函数的一部分,用于给结果集进行分区。它和聚合函数Group by不同的地方在于它只是将原始数据进行名次排列,能够返回一个分组中的多条记录(记录数不变),而Group by是对原始数据进行聚合统计,一般只有一条反映统计值的结果(每组返回一条)。
注意事项:
1、使用rank over()的时候,空值是最大的,如果排序字段为null, 可能造成null字段排在最前面,影响排序结果。
可以这样: rank over(partition by course order by score desc nulls last)
2、排名函数必须有order by 的over子句
3、分组内从1开始排序