在Oracle日常的数据处理中，某些情况下，我们需要对数据进行排名处理，比如：在统计学生成绩时，都会使用到排名的情况，那在Oracle中排名函数是那些？又是如何使用的呢，本文将通过实例来详细分析相关用法，供大家在类似应用中参考使用。

在Oracle中，目前已知的排名函数如下：
1、rank 函数，并列时排名相同，占用名次，不连续，空值最大；
2、dense_rank 函数，并列时排名相同，不占用名次，连续，空值最大；
3、row_number 函数，分区内名次连续，空值最大；

函数语法：
rank() over(order by 排序字段 顺序)
rank() over(partition by 分组字段 order by 排序字段 顺序)
dense_rank() over(order by 排序字段 顺序)
dense_rank() over(partition by 分组字段 order by 排序字段 顺序)
row_number() over(order by 排序字段 顺序)
row_number() over(partition by 分组字段 order by 排序字段 顺序)

一、创建测试表，添加测试数据
drop table my_test;
create table my_test
(
 autoid      number primary key,
 stu_id      number(3),
 stu_name    char(8) not null,
 sub_name    varchar2(20),
 score       number(10,2)
);
insert into my_test (autoid, stu_id, stu_name, sub_name, score)
values (8, 1, '张三 ', '语文', 80);
insert into my_test (autoid, stu_id, stu_name, sub_name, score)
values (9, 2, '李四 ', '数学', 80);
insert into my_test (autoid, stu_id, stu_name, sub_name, score)
values (10, 1, '张三 ', '数学', 0);
insert into my_test (autoid, stu_id, stu_name, sub_name, score)
values (11, 2, '李四 ', '语文', 50);
insert into my_test (autoid, stu_id, stu_name, sub_name, score)
values (12, 3, '王五 ', '语文', 10);
insert into my_test (autoid, stu_id, stu_name, sub_name, score)
values (13, 3, '王五 ', '数学', null);
insert into my_test (autoid, stu_id, stu_name, sub_name, score)
values (14, 3, '王五 ', '体育', 120);
insert into my_test (autoid, stu_id, stu_name, sub_name, score)
values (15, 4, '王二 ', ';
insert into my_test (autoid, stu_id, stu_name, sub_name, score)
values (16, 5, '麻子 ', 'c++', 80);
insert into my_test (autoid, stu_id, stu_name, sub_name, score)
values (3, 3, '王五 ', 'oracle', 0);
insert into my_test (autoid, stu_id, stu_name, sub_name, score)
values (4, 4, '王二 ', 'oracle', 77);
insert into my_test (autoid, stu_id, stu_name, sub_name, score)
values (17, 2, '李四 ', 'oracle', 77);
commit;
 

二、实例
1、查询原始数据：学号,姓名,科目名称,成绩
SQL> select * from my_test;
    AUTOID STU_ID STU_NAME SUB_NAME                    SCORE
---------- ------ -------- -------------------- ------------
         8      1 张三     语文                        80.00
         9      2 李四     数学                        80.00
        10      1 张三     数学                         0.00
        11      2 李四     语文                        50.00
        12      3 王五     语文                        10.00
        13      3 王五     数学                 
        14      3 王五     体育                       120.00
        15      4 王二     java                        90.00
        16      5 麻子     c++                         80.00
         3      3 王五     oracle                       0.00
         4      4 王二     oracle                      77.00
        17      2 李四     oracle                      77.00
12 rows selected


2、通过rank()函数对各学生科目为oracle排名(简单排名)
SQL> select t.stu_id,t.stu_name,t.sub_name,t.score,
  2         rank() over(order by score desc) 名次
  3  from my_test t
  4  where sub_name='oracle';
STU_ID STU_NAME SUB_NAME                    SCORE         名次
------ -------- -------------------- ------------ ----------
     4 王二     oracle                      77.00          1
     2 李四     oracle                      77.00          1
     3 王五     oracle                       0.00          3

注意：使用rank()函数时，名次为非连续排名，即并列名次占用了排名

3、通过dense_rank()函数对各学生科目为oracle排名(简单排名)
SQL> select t.stu_id,t.stu_name,t.sub_name,t.score,
  2         dense_rank() over (order by score desc) 名次
  3  from my_test t
  4  where sub_name='oracle';
STU_ID STU_NAME SUB_NAME                    SCORE         名次
------ -------- -------------------- ------------ ----------
     4 王二     oracle                      77.00          1
     2 李四     oracle                      77.00          1
     3 王五     oracle                       0.00          2
     
注意：使用dense_rank()函数时，名次为连续排名，即并列名次没有占用排名


4、通过rank()函数查询各学生各科排名(分区排名)
SQL> select stu_id,stu_name,sub_name,score,
  2         rank() over(partition by sub_name order by score desc) 名次
  3  from my_test;
STU_ID STU_NAME SUB_NAME                    SCORE         名次
------ -------- -------------------- ------------ ----------
     5 麻子     c++                         80.00          1
     4 王二     java                        90.00          1
     4 王二     oracle                      77.00          1
     2 李四     oracle                      77.00          1
     3 王五     oracle                       0.00          3
     3 王五     体育                       120.00          1
     3 王五     数学                                       1
     2 李四     数学                        80.00          2
     1 张三     数学                         0.00          3
     1 张三     语文                        80.00          1
     2 李四     语文                        50.00          2
     3 王五     语文                        10.00          3
12 rows selected


5、通过dense_rank()函数查询各科前2名(分区排名)
类似新闻表,求栏目点击率在前3位的新闻。商品表,求各类别销售额在前10位的商品。
SQL> select *
  2  from (
  3       select sc.stu_id,sc.stu_name,sub_name,sc.score,
  4       dense_rank() over(partition by sub_name order by score desc) 名次
  5       from my_test sc
  6  )t
  7  where t.名次<=2;
STU_ID STU_NAME SUB_NAME                    SCORE         名次
------ -------- -------------------- ------------ ----------
     5 麻子     c++                         80.00          1
     4 王二     java                        90.00          1
     4 王二     oracle                      77.00          1
     2 李四     oracle                      77.00          1
     3 王五     oracle                       0.00          2
     3 王五     体育                       120.00          1
     3 王五     数学                                       1
     2 李四     数学                        80.00          2
     1 张三     语文                        80.00          1
     2 李四     语文                        50.00          2
10 rows selected
 

6、通过sum()函数查询各同学总分
SQL> select stu_id,stu_name,sum(score) sum_score
  2  from my_test
  3  group by stu_id,stu_name;
STU_ID STU_NAME  SUM_SCORE
------ -------- ----------
     4 王二            167
     3 王五            130
     5 麻子             80
     2 李四            207
     1 张三             80


7、通过rank()函数根据总分查询各同学名次
SQL> select t.*,rank() over (order by sum_score desc) 名次
  2  from (
  3       select stu_id,stu_name,sum(score)sum_score
  4       from my_test
  5       group by stu_id,stu_name
  6  )t;
STU_ID STU_NAME  SUM_SCORE         名次
------ -------- ---------- ----------
     2 李四            207          1
     4 王二            167          2
     3 王五            130          3
     1 张三             80          4
     5 麻子             80          4
     
8、通过row_number()函数根据科目名次分区查询成绩排名
SQL> select t.*,
  2         row_number() over(partition by sub_name order by score) as 名次
  3  from my_test t;
    AUTOID STU_ID STU_NAME SUB_NAME                    SCORE         名次
---------- ------ -------- -------------------- ------------ ----------
        16      5 麻子     c++                         80.00          1
        15      4 王二     java                        90.00          1
         3      3 王五     oracle                       0.00          1
         4      4 王二     oracle                      77.00          2
        17      2 李四     oracle                      77.00          3
        14      3 王五     体育                       120.00          1
        10      1 张三     数学                         0.00          1
         9      2 李四     数学                        80.00          2
        13      3 王五     数学                                       3
        12      3 王五     语文                        10.00          1
        11      2 李四     语文                        50.00          2
         8      1 张三     语文                        80.00          3
12 rows selected

注意：row_number()函数在各分区内为连续排名

9、通过row_number()函数对分数排名
SQL> select t.*,row_number() over(order by score desc) mc
  2  from my_test t;
    AUTOID STU_ID STU_NAME SUB_NAME                    SCORE         MC
---------- ------ -------- -------------------- ------------ ----------
        13      3 王五     数学                                       1
        14      3 王五     体育                       120.00          2
        15      4 王二     java                        90.00          3
         8      1 张三     语文                        80.00          4
        16      5 麻子     c++                         80.00          5
         9      2 李四     数学                        80.00          6
         4      4 王二     oracle                      77.00          7
        17      2 李四     oracle                      77.00          8
        11      2 李四     语文                        50.00          9
        12      3 王五     语文                        10.00         10
         3      3 王五     oracle                       0.00         11
        10      1 张三     数学                         0.00         12
12 rows selected


三、总结
1、注意空值对名次的影响，一般空值为最大；
2、顺序：asc|desc 名次与业务相关
3、分区字段：根据什么字段进行分区，通过partition函数进行分区排名，可获取分区排名情况。

分区与分组有什么区别：
1、分区只是将原始数据进行名次排列(记录数不变),
2、分组是对原始数据进行聚合统计(记录数变少,每组返回一条),注意：聚合。





本文主要参考资料：
http://www.jb51.net/article/51627.htm
http://www.linuxidc.com/Linux/2015-04/116349.htm