Oracle 的几种排名方式

  在写oracle排名的时候，一开始我天真的认为，只要用rownum 去实现就可以了，然后我变这样写了

  SQL>  select a.*,rownum as "名字" from emp a;语句其运行效果如下：

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	名次
7369	SMITH	CLERK	7902	1980/12/17	800.00		20	1
7499	ALLEN	SAKESMAN	7698	1981/2/20	1600.00		30	2
7521	WARD	SAKESMAN	7698	1981/2/22	1250.00		30	3
7566	JONES	MANAGER	7839	1981/4/2	2975.00		20	4
7654	WARTIN	SAKESMAN	7698	1981/9/28	1250.00		30	5
7698	BLAKE	MANAGER	7839	1981/5/1	2850.00		30	6
7782	CLARK	MANAGER	7839	1981/6/9	2450.00		10	7
7788	SCOTT	ANALYST	7566	1982/12/9	3000.00		20	8
7839	KING	PRESIDENT		1981/11/17	5000.00		10	9
7844	TURNER	SALESMAN	7698	1981/9/8	1500.00		30	10
7876	ADAMS	CLERK	7788	1983/1/12	1100.00		20	11
7900	JAMES	CLERK	7698	1981/12/3	950.00		30	12
7902	FORD	ANALYST	7566	1981/12/3	3000.00		20	13
7934	MILIER	CLERK	7782	1982/1/23	1300.00		10	14

结果怎好是我们想要的那种排名，我想这样应该一目了然了吧

  效果看起来蛮对昂，但是呢我们现在把语句改写成如下：

  SQL> select a.*,rownum as "名字" from emp a order by a.sal desc;

我天真的认为rownum 记录的数据应该和我想要的数据相吻合，但是结果如下：（毕竟rownum是用来分页的嘛）

EMPNO  ENAME  JOB  MGR  HIREDATE   SAL  COMM  DEPTNO 名次 

7839

KING CLERK 7902 

1980/12/17 

5000.00      10   9 

 7902 

FORD SAKESMAN  7698

1981/2/20 

3000.00      30  13

7788

SCOTT  SAKESMAN   7698 

1981/2/22 

3000.00     30  8 

7566 

JONES  MANAGER  7839 

1981/4/2 

2975.00     20  4 

7698

BLAKE  SAKESMAN   7698

1981/9/28 

2850.00      30   6 

7782

CLARK  MANAGER  7839 

1981/5/1  

2450.00     30  7 

7499

ALLEN  MANAGER   7839 

1981/6/9 

1600.00     10  2 

7844

TURNER  ANALYST  7566 

1982/12/9 

1500.00     20  10 

7934

MILIER PRESIDENT   

1981/11/17 

1300.00     10  14 

7521

 WARD SALESMAN  7698  1981/9/8  1250.00     30  3 

7654

 MARTIN CLERK  7788  1983/1/12  1250.00     20  5 

 7876 

ADAMS CLERK  7698  1981/12/3  1100.00     30  11 

 7900

JAMES ANALYST  7566  1981/12/3  950.00     20  12 

7396

SMITH CLERK 7782 1982/1/23 800.00    10 1

 完全就是哎（无语）

也许我们在开发中会去到这样或者那样的排序问题，如根据工资进行降序排列并且要求我们要两个并列的出现以后，其后的数据都要相应加1（好比两个第一下面再排就是第3没有第二）那我们要怎么去实现呢要想用上面的方法去实现是不可能了，要想用case去实现我想希望也不大，所以我们下面就介绍几种oracle的排名函数

1．dense_rank() 函数

SQL> select a.*,dense_rank() over(PARTITION BY deptno ORDER BY nvl(sal,0) DESC) as　"名次" from emp a;

我们用dense_rank()函数和over()按照部门分组并按照工资降序进行排名 

EMPNO  ENAME  JOB  MGR  HIREDATE   SAL  COMM  DEPTNO 名次 

7839

KING CLERK 7902 

1980/12/17 

5000.00      10   1 

 7902 

FORD SAKESMAN  7698

1981/2/20 

2450.00      10  2

7788

SCOTT  SAKESMAN   7698 

1981/2/22 

1300.00     10 3

7566 

JONES  MANAGER  7839 

1981/4/2 

3000.00     20  1 

7698

BLAKE  SAKESMAN   7698

1981/9/28 

3000.00      20   1 

7782

CLARK  MANAGER  7839 

1981/5/1  

2975.00     20  2 

7499

ALLEN  MANAGER   7839 

1981/6/9 

1100.00     20  3 

7844

TURNER  ANALYST  7566 

1982/12/9 

800.00     20  4 

7934

MILIER PRESIDENT   

1981/11/17 

2850.00     30  1 

7521

 WARD SALESMAN  7698  1981/9/8  1600.00     30  2 

7654

 MARTIN CLERK  7788  1983/1/12  1500.00     30  3 

 7876 

ADAMS CLERK  7698  1981/12/3  1250.00     30  4 

 7900

JAMES ANALYST  7566  1981/12/3  1250.00     30 4

7396

SMITH CLERK 7782 1982/1/23 950.00    30 5

 结果就是每个部门之间进行了排名，并且实现了并列名次只是没有实现我们想要的结果

     假如我们把over()里的PARTITION BY deptno 去掉回事什么效果呢

     SQL> select a.*,dense_rank() over(ORDER BY nvl(sal,0) DESC) as　"名次" from emp a; 

EMPNO  ENAME  JOB  MGR  HIREDATE   SAL  COMM  DEPTNO 名次 

7839

KING CLERK 7902 

1980/12/17 

5000.00      10   1 

 7902 

FORD SAKESMAN  7698

1981/2/20 

3000.00      20  2

7788

SCOTT  SAKESMAN   7698 

1981/2/22 

3000.00     20  2 

7566 

JONES  MANAGER  7839 

1981/4/2 

2975.00     20  3 

7698

BLAKE  SAKESMAN   7698

1981/9/28 

2850.00      30   4 

7782

CLARK  MANAGER  7839 

1981/5/1  

2450.00     10  5 

7499

ALLEN  MANAGER   7839 

1981/6/9 

1600.00     30  6 

7844

TURNER  ANALYST  7566 

1982/12/9 

1500.00     30  7 

7934

MILIER PRESIDENT   

1981/11/17 

1300.00     10  8 

7521

 WARD SALESMAN  7698  1981/9/8  1250.00     30  9 

7654

 MARTIN CLERK  7788  1983/1/12  1250.00     30  9 

 7876 

ADAMS CLERK  7698  1981/12/3  1100.00     20  10 

 7900

JAMES ANALYST  7566  1981/12/3  950.00     30  11 

7396

SMITH CLERK 7782 1982/1/23 800.00    20 12

 

结果就是整体进行了排名，并且实现了并列名次只是没有实现我们想要的结果(而且工资也不是按部门降序了)

2．ROW_NUMBER() 函数

  SQL> select a.*,ROW_NUMBER() over(ORDER BY nvl(sal,0) DESC) as　"名次" from emp a;

 

 

EMPNO  ENAME  JOB  MGR  HIREDATE   SAL  COMM  DEPTNO 名次 

7839

KING CLERK 7902 

1980/12/17 

5000.00      10   1 

 7902 

FORD SAKESMAN  7698

1981/2/20 

3000.00      20  2

7788

SCOTT  SAKESMAN   7698 

1981/2/22 

3000.00     20  3 

7566 

JONES  MANAGER  7839 

1981/4/2 

2975.00     20  4 

7698

BLAKE  SAKESMAN   7698

1981/9/28 

2850.00      30   5 

7782

CLARK  MANAGER  7839 

1981/5/1  

2450.00     10  6 

7499

ALLEN  MANAGER   7839 

1981/6/9 

1600.00     30  7 

7844

TURNER  ANALYST  7566 

1982/12/9 

1500.00     30  8 

7934

MILIER PRESIDENT   

1981/11/17 

1300.00     10  9 

7521

 WARD SALESMAN  7698  1981/9/8  1250.00     30  10 

7654

 MARTIN CLERK  7788  1983/1/12  1250.00     30  11 

 7876 

ADAMS CLERK  7698  1981/12/3  1100.00     20  12 

 7900

JAMES ANALYST  7566  1981/12/3  950.00     30  13 

7396

SMITH CLERK 7782 1982/1/23 800.00    20 14

 

 

 结果显示这个函数为我们进行了我们惯性思维认可的排名，但是我们怎样才能够实现上面做说的那种并列排名（好比两个第一下面再排就是第3没有第二）;看下面这个函数

3．RANK ()

结果怎好是我们想要的那种排名，我想这样应该一目了然了吧（由于字数限制正解只要把函数换为rank()即可）