排序函数 rank() dense_rank()

排序函数：
1.rank() over(partition by 分组项 order by 排序项  [desc|asc])
2.dense_rank() over(partition by 分组项 order by 排序项  [desc|asc])
区别：
dence_rank在并列关系后，不会跳过等级。rank则跳过。
例子：
1.select sal,rank() over(partition by deptno order by sal desc) from emp;（跳过）

       SAL RANK()OVER(PARTITIONBYDEPTNOORDERBYSALDESC)
---------- -------------------------------------------
      5000                                           1
      2450                                           2
      1300                                           3
      3000                                           1
      3000                                           1
      2975                                           3
      1100                                           4
       800                                           5
      2850                                           1
      1600                                           2
      1500                                           3

       SAL RANK()OVER(PARTITIONBYDEPTNOORDERBYSALDESC)
---------- -------------------------------------------
      1250                                           4
      1250                                           4
       950                                           6

2.select deptno,sal,dense_rank() over(partition by deptno order by sal desc)  （不跳过）
  from emp;

    DEPTNO        SAL DENSE_RANK()OVER(PARTITIONBYDEPTNOORDERBYSALDESC)
---------- ---------- -------------------------------------------------
        10       5000                                                 1
        10       2450                                                 2
        10       1300                                                 3
        20       3000                                                 1
        20       3000                                                 1
        20       2975                                                 2
        20       1100                                                 3
        20        800                                                 4
        30       2850                                                 1
        30       1600                                                 2
        30       1500                                                 3

    DEPTNO        SAL DENSE_RANK()OVER(PARTITIONBYDEPTNOORDERBYSALDESC)
---------- ---------- -------------------------------------------------
        30       1250                                                 4
        30       1250                                                 4
        30        950                                                 5

3.合计排序功能：计算出数值(4,1)在Orade By Col1,Col2排序下的排序值，也就是col1=4,col2=1在排序以后的位置
　　SELECT RANK(4,1) WITHIN GROUP (ORDER BY col1,col2) "Rank" FROM table;
　　结果如下：

　　Rank
　　4

  通过以上方法，得出col1为4，col2为1的那行数据的rank排名为多少

 

转载于:https://www.cnblogs.com/sisier/p/4661031.html