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()即可）