SQL语句之分组排名

现在有这样一个需求，需要查找出emp表中各部门员工工资排名前三的员工，员工号和工资。

SQL> select ename,empno,deptno,sal,
  2  row_number() over(partition by deptno order by sal desc) as row_number,
  3  rank() over(partition by deptno order by sal desc) as rank,
  4  dense_rank() over(partition by deptno order by sal desc) as dense_rank
  5  from emp
  6  order by 3 desc;

ENAME       EMPNO     DEPTNO    SAL ROW_NUMBER       RANK DENSE_RANK
---------- ---------- ---------- ---------- ---------- ---------- ----------
BLAKE        7698         30       2850      1      1      1
ALLEN        7499         30       1600      2      2      2
TURNER       7844         30       1500      3      3      3
WARD         7521         30       1250      4      4      4
MARTIN       7654         30       1250      5      4      4
JAMES        7900         30    950      6      6      5
FORD         7902         20       3000      1      1      1
SCOTT        7788         20       3000      2      1      1
JONES        7566         20       2975      3      3      2
ADAMS        7876         20       1100      4      4      3
SMITH        7369         20    800      5      5      4
KING         7839         10       5000      1      1      1
CLARK        7782         10       2450      2      2      2
MILLER       7934         10       1300      3      3      3

14 rows selected.

SQL> 

如上所述，上面使用到了三个函数
分别是
row_number() over(partition by [ ])
rank() over(partition by [])
dense_rank() over(partition by [])
这三个函数，通过结果我们发现
*区别：*
RANK 相同的工资会生成相同的序号，而且其后面的序号与 ROW_NUMBER相同。
DENSE_RANK 相同的工资会生成同样的序号，而且其后面的序号递增。

当然也可以使用普通查询语句来查询出来

SQL> select ename,empno,deptno,sal
  2  from emp e1
  3  where
  4  (select count(*) from emp e2 where e1.empno=e2.empno and e1.sal>=e2.sal) <=3
  5  order by deptno desc;

ENAME       EMPNO     DEPTNO    SAL
---------- ---------- ---------- ----------
BLAKE        7698         30       2850
TURNER       7844         30       1500
ALLEN        7499         30       1600
MARTIN       7654         30       1250
WARD         7521         30       1250
JAMES        7900         30    950
SCOTT        7788         20       3000
JONES        7566         20       2975
SMITH        7369         20    800
ADAMS        7876         20       1100
FORD         7902         20       3000
KING         7839         10       5000
MILLER       7934         10       1300
CLARK        7782         10       2450

14 rows selected.

也可以使用Oracle的分区功能：

SQL> select ename,empno,deptno,sal
  2  from 
  3  (select ename,empno,deptno,sal,row_number() over (partition by deptno order by sal desc) as rn       
  4  from emp)
  5  where rn <= 3;

ENAME       EMPNO     DEPTNO    SAL
---------- ---------- ---------- ----------
KING         7839         10       5000
CLARK        7782         10       2450
MILLER       7934         10       1300
SCOTT        7788         20       3000
FORD         7902         20       3000
JONES        7566         20       2975
BLAKE        7698         30       2850
ALLEN        7499         30       1600
TURNER       7844         30       1500

9 rows selected.

SQL>