Rank()使用说明：

 

a. 函数简介：

    返回结果集分区内指定字段的值的排名，指定字段的值的排名是相关行之前的排名加一。

b. 语法：

    RANK() OVER([<partiton_by_clause>]<order by clause>)

c. 参数说明：

    partition_by_clause 将from子句生成的结果集划分为应用到RANK函数的分区。

    Order_by_clause确定将RANK值应用到分区中的行时所使用的顺序。

 

d. 以下是实例使用：

 

 

1. 创建测试表

 

Sql代码   oracle排名函数使用_sql
  1. --创建表  
  2. -- Create table  
  3. create table T_SCORE  
  4. (  
  5.   AUTOID   NUMBER not null,  
  6.   S_ID     NUMBER(3),  
  7.   S_NAME   CHAR(8) not null,  
  8.   SUB_NAME VARCHAR2(20),  
  9.   SCORE    NUMBER(10,2)  
  10. );  
  11. -- Add comments to the table   
  12. comment on table T_SCORE  
  13.   is '学生成绩表';  
  14. -- Add comments to the columns   
  15. comment on column T_SCORE.AUTOID  
  16.   is '主键ID';  
  17. comment on column T_SCORE.S_ID  
  18.   is '学生ID';  
  19. comment on column T_SCORE.S_NAME  
  20.   is '学生姓名';  
  21. comment on column T_SCORE.SUB_NAME  
  22.   is '科目';  
  23. comment on column T_SCORE.SCORE  
  24.   is '成绩';  

 

2. 创建测试记录

 

Sql代码   oracle排名函数使用_sql
  1. insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)  
  2. values (8, 1, '张三    ', '语文', 80.00);  
  3.   
  4. insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)  
  5. values (9, 2, '李四    ', '数学', 80.00);  
  6.   
  7. insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)  
  8. values (10, 1, '张三    ', '数学', 0.00);  
  9.   
  10. insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)  
  11. values (11, 2, '李四    ', '语文', 50.00);  
  12.   
  13. insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)  
  14. values (12, 3, '张三丰  ', '语文', 10.00);  
  15.   
  16. insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)  
  17. values (13, 3, '张三丰  ', '数学', null);  
  18.   
  19. insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)  
  20. values (14, 3, '张三丰  ', '体育', 120.00);  
  21.   
  22. insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)  
  23. values (15, 4, '杨过    ', 'JAVA', 90.00);  
  24.   
  25. insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)  
  26. values (16, 5, 'mike    ', 'c++', 80.00);  
  27.   
  28. insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)  
  29. values (3, 3, '张三丰  ', 'Oracle', 0.00);  
  30.   
  31. insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)  
  32. values (4, 4, '杨过    ', 'Oracle', 77.00);  
  33.   
  34. insert into t_score (AUTOID, S_ID, S_NAME, SUB_NAME, SCORE)  
  35. values (17, 2, '李四    ', 'Oracle', 77.00);  

 

3. 分不同情况查询

3.1 查询所有的学生成绩

 

Sql代码   oracle排名函数使用_sql
  1. --1.查询所有的学生成绩  
  2. select t.s_id 学号, t.s_name 姓名, t.sub_name 科目, t.score 成绩  
  3.   from t_score t;  

查询结果：

学号 姓名 科目 成绩
1 张三     语文 80.00
2 李四     数学 80.00
1 张三     数学 0.00
2 李四     语文 50.00
3 张三丰   语文 10.00
3 张三丰   数学  
3 张三丰   体育 120.00
4 杨过     JAVA 90.00
5 mike     c++ 80.00
3 张三丰   Oracle 0.00
4 杨过     Oracle 77.00
2 李四     Oracle 77.00

 

3.2 查询Oracle科目成绩名次-非连续rank

Sql代码   oracle排名函数使用_sql
  1. --2.查询Oracle科目成绩名次-非连续rank  
  2. select t.s_id 学号,  
  3.        t.s_name 姓名,  
  4.        t.sub_name 科目,  
  5.        t.score 成绩,  
  6.        rank() over(order by score desc nulls last) 名次  
  7.   from t_score t  
  8.  where t.sub_name = 'Oracle';  

 查询结果：

学号 姓名 科目 成绩 名次
4 杨过     Oracle 77.00 1
2 李四     Oracle 77.00 1
3 张三丰   Oracle 0.00 3

 

3.3查询Oracle科目成绩名次-连续dense_rank

Sql代码   oracle排名函数使用_sql
  1. --3.查询Oracle科目成绩名次-连续dense_rank  
  2. select t.s_id 学号,  
  3.        t.s_name 姓名,  
  4.        t.sub_name 科目,  
  5.        t.score 成绩,  
  6.        dense_rank() over(order by score desc nulls last) 名次  
  7.   from t_score t  
  8.  where t.sub_name = 'Oracle';  

 查询结果：

 

学号 姓名 科目 成绩 名次
4 杨过     Oracle 77.00 1
2 李四     Oracle 77.00 1
3 张三丰   Oracle 0.00 2

 

3.4 查询各学生各科排名

Sql代码   oracle排名函数使用_sql
  1. --4.查询各学生各科排名  
  2. select t.s_id 学号,  
  3.        t.s_name 姓名,  
  4.        t.sub_name 科目,  
  5.        t.score 成绩,  
  6.        dense_rank() over(partition by t.s_name order by score desc nulls last) 名次  
  7.   from t_score t;  

 查询结果：

 

学号 姓名 科目 成绩 名次
5 mike     c++ 80.00 1
2 李四     数学 80.00 1
2 李四     Oracle 77.00 2
2 李四     语文 50.00 3
4 杨过     JAVA 90.00 1
4 杨过     Oracle 77.00 2
1 张三     语文 80.00 1
1 张三     数学 0.00 2
3 张三丰   体育 120.00 1
3 张三丰   语文 10.00 2
3 张三丰   Oracle 0.00 3
3 张三丰   数学   4

 

3.5 查询各科名次（分区）

Sql代码   oracle排名函数使用_sql
  1. --5.查询各科名次（分区）  
  2. select t.s_id 学号,  
  3.        t.s_name 姓名,  
  4.        t.sub_name 科目,  
  5.        t.score 成绩,  
  6.        dense_rank() over(partition by t.sub_name order by score desc nulls last) 名次  
  7.   from t_score t;  

 查询结果：

 

学号 姓名 科目 成绩 名次
4 杨过     JAVA 90.00 1
4 杨过     Oracle 77.00 1
2 李四     Oracle 77.00 1
3 张三丰   Oracle 0.00 2
5 mike     c++ 80.00 1
2 李四     数学 80.00 1
1 张三     数学 0.00 2
3 张三丰   数学   3
3 张三丰   体育 120.00 1
1 张三     语文 80.00 1
2 李四     语文 50.00 2
3 张三丰   语文 10.00 3