rank() over(order by列名)的用法


		rank() over(order by列名)的用法
 
[sql] 
  
---rank()over(order by 列名排序)的结果是不连续的，如果有4个人，其中有3个是并列第1名，那么最后的排序结果结果如：1 1 1 4
[sql] 
select scoreid, studentid,COURSENAME,totalexamscore ,  
rank()over(order by TOTALEXAMSCORE desc)orderbyNum  
from CJ_SCORECOURSE a ,CJ_COURSESCORE b  
where a.SCORECOURSEID = b.SCORECOURSEID  
 
---dense_rank()over(order by 列名排序)的结果是连续的，如果有4个人，其中有3个是并列第1名，那么最后的排序结果如：1 1 1 2
 
[sql] 
SQL> select ename, sal,rank() over(order by coalesce(sal,0) desc) from emp;  
  
ENAME             SAL RANK()OVER(ORDERBYCOALESCE(SAL,0)DESC)  
---------- ---------- --------------------------------------  
KING             5000                                      1  
FORD             3000                                      2  
SCOTT            3000                                      2  
JONES            2975                                      4  
BLAKE            2850                                      5  
CLARK            2450                                      6  
ALLEN            1600                                      7  
TURNER           1500                                      8  
MILLER           1300                                      9  
WARD             1250                                     10  
MARTIN           1250                                     10  
  
ENAME             SAL RANK()OVER(ORDERBYCOALESCE(SAL,0)DESC)  
---------- ---------- --------------------------------------  
ADAMS            1100                                     12  
JAMES             950                                     13  
SMITH             800                                     14  
jack                                                      15  
 
----rank () OVER (PARTITION BY 列名 ORDER BY 列名排序)使用分区方式获取每门课程的最高分
SELECT *
FROM (SELECT scoreid, studentid, coursename, TOTALEXAMSCORE,
rank () OVER (PARTITION BY coursename ORDER BY TOTALEXAMSCORE DESC)orderbynum
FROM cj_scorecourse a, cj_coursescore b
WHERE a.scorecourseid = b.scorecourseid and studentID = '62000DA50FC14C7FE040007F01002A63')
WHERE orderbynum < 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 排序字段 顺序)
rank() over (partition by 分组字段 order by 排序字段 顺序)
 
1.顺序：asc|desc 名次与业务相关：
示例：求优秀学员，成绩：降序 迟到次数：升序
2.分区字段：根据什么字段进行分区。
 
问题：分区与分组有什么区别?
?分区只是将原始数据进行名次排列(记录数不变),
?分组是对原始数据进行聚合统计(记录数变少,每组返回一条)。
 
注意：使用rank()over(order by 排序字段 顺序)排序的时候，空值是最大的
 
如果排序字段为null，可能造成在排序时将null字段排在最前面，影响排序的正确性。
所以建议将dense_rank()over(order by 列名 排序)改为dense_rank()over(order by 列名排序 nulls last)
这样只要排序字段为null,就会放在最后，而不会影响排序结果