Oracle 中rank() 对表中的数据进行分级排序

摘自：http://oracle.chinaitlab.com/exploiture/831910.html

 

rank()对表中的数据进行分级排序

譬如有张学生成绩统计单的表STUDENT_RESULT

　　name number kemu fenshu

　　li 0113101 高数 90

　　zhang 0113098 高数 80

　　wang 0113077 高数 70

　　li 0113101 物理 80

　　zhang 0113098 物理 90

　　wang 0113077 物理 70

　　我如果要检索出表中高数的前两名和物理的前两名

　那么可以通过使用 RANK()方法达到目的

　　--首先 通过kemu进行分类，然后按照fenshu降序排序

　　select rank() over(partition by kemu order by fenshu desc) rk,t.* from

　　student_result t

　　结果

　　rk name number kemu fenshu

　　1 li 0113101 高数 90

　　2 zhang 0113098 高数 80

　　3 wang 0113077 高数 70

　　1 zhang 0113098 物理 90

　　2 li 0113101 物理 80

　　3 wang 0113077 物理 70

--然后，检索出rk<=2的记录，即取出前两名

　　select * from (select rank() over(partition by kemu order by fenshu desc)

　　rk,t.* from student_result t) as y where y.rk<=2;

　　结果

　　rk name number kemu fenshu

　　1 li 0113101 高数 90

　　2 zhang 0113098 高数 80

　　1 zhang 0113098 物理 90

　　2 li 0113101 物理 80

dense_rank()和 rank()的用法完全相同，不同的是在出现分数相同的情况下

select rank() over(order by fenshu desc) rk,t.* from student_result t

　　的结果为

　　rk name number kemu fenshu

　　1 zhang 0113098 物理 90

　　2 li 0113101 高数 80

　　2 zhang 0113098 高数 80

　　2 li 0113101 物理 80

　　5 wang 0113077 高数 70

　　5 wang 0113077 物理 70

　　而select dense_rank() over(order by fenshu desc) rk,t.* from student_result t

　　结果为

　　rk name number kemu fenshu

　　1 zhang 0113098 物理 90

　　2 li 0113101 高数 80

　　2 zhang 0113098 高数 80

　　2 li 0113101 物理 80

　　3 wang 0113077 高数 70

　　3 wang 0113077 物理 70

 

摘自：http://blog.sina.com.cn/s/blog_713978a5010102ej.html

注：

---rank()  over(order by 列名排序）的结果是不连续的，如果有4个人，其中有3个是并列第1名，那么最后排序的结果如：1 1 1 4

---dense_rank() over( order by 列名排序)的结果是连续的，如果有4个人，其中有3个是并列第1名，那么最后排序的结果如：1 1 1 2

-----使用over实现成绩求和
-----SUM (totalexamscore) OVER (ORDER BY studentid) sum1 实现的是连续求和，如第一个学生的总评成绩是30，则sum1就展示为30，到第二个学生成绩出现的时候，则会依次累加
-----SUM (totalexamscore) OVER () sum2 就相当于是单纯的求和，和直接使用sum是一致的
SELECT scoreid, studentid, totalexamscore,
SUM (totalexamscore) OVER (ORDER BY studentid) sum1,
SUM (totalexamscore) OVER () sum2
FROM cj_coursescore

 

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

注意：使用rank()over(order by 排序字段 顺序)排序的时候，空值是最大的

如果排序字段为null，可能造成在排序时将null字段排在最前面，影响排序的正确性。
所以建议将dense_rank()over(order by 列名 排序)改为dense_rank()over(order by 列名排序 nulls last)
这样只要排序字段为null,就会放在最后，而不会影响排序结果