row_number() /rank()/dense_rank()

rank()函数主要用于排序，并给出序号
dense_rank()：功能同rank()一样，区别在于，rank()对于排序并的数据给予相同序号，接下来的数据序号直接跳中跃，dense_rank()则不是
row_number()涵数则是按照顺序依次使用，相当于我们普通查询里的rownum值
比如数据：
1，2，2，4，5，6。。。这是rank()的形式 
1，2，2，3，4，5。。。这是dense_rank()的形式
1，2，3，4，5，6。。。这是row_number()涵数形式

--13，取积分排名
select pjs.sn_operatorid 人员ID,
       pjs.sn_scores 积分,
       dense_rank() over(order by pjs.sn_scores desc) as dense_rank方式,
       rank() over(order by pjs.sn_scores desc) as rank方式,
       row_number() over(order by pjs.sn_scores desc) as row_number方式
  from cpmos.pxks_j_scoresum pjs

 

本文来自:http://googledave.iteye.com/blog/452442  和 http://theron19.iteye.com/blog/710585

 

有些时候我们希望得到指定数据中的前n列，示例如下：

得到每个部门薪水最高的三个雇员：

先创建示例表 

 

create table emp as select * from scott.emp; alter table emp add constraint emp_pk primary key(empno); create table dept as select * from scott.dept; alter table dept add constraint dept_pk primary key(deptno);

 

先看一下row_number() /rank()/dense_rank()三个函数之间的区别
select emp.deptno,emp.sal,emp.empno,row_number() over (partition by deptno order by sal desc) row_number, --1,2,3 rank() over (partition by deptno order by sal desc) rank, --1,1,3 dense_rank() over (partition by deptno order by sal desc) dense_rank from emp --1,1,2

 结果如下:

10 5000.00 7839 1 1 1 10 2450.00 7782 2 2 2 10 1300.00 7934 3 3 3 20 3000.00 7788 1 1 1 20 3000.00 7902 2 1 1 20 2975.00 7566 3 3 2 20 1100.00 7876 4 4 3 20 800.00 7369 5 5 4 30 2850.00 7698 1 1 1 30 1600.00 7499 2 2 2

 取每个部门的薪水前三位雇员：

select t.deptno,t.rank,t.sal from ( select emp.*,row_number() over (partition by deptno order by sal desc) row_number, --1,2,3 rank() over (partition by deptno order by sal desc) rank, --1,1,3 dense_rank() over (partition by deptno order by sal desc) dense_rank from emp --1,1,2 ) t where t.rank<=3

 结果如下：

10 1 5000.00 10 2 2450.00 10 3 1300.00 20 1 3000.00 20 1 3000.00 20 3 2975.00 30 1 2850.00 30 2 1600.00 30 3 1500.00

 如果想输出成deptno  sal1   sal2   sal3这种类型的格式
步骤一（decode）：

select t.deptno,decode(row_number,1,sal) sal1,decode(row_number,2,sal) sal2,decode(row_number,3,sal) sal3 from ( select emp.*,row_number() over (partition by deptno order by sal desc) row_number, --1,2,3 rank() over (partition by deptno order by sal desc) rank, --1,1,3 dense_rank() over (partition by deptno order by sal desc) dense_rank from emp --1,1,2 ) t where t.rank<=3

 结果如下：

10 5000 10 2450 10 1300 20 3000 20 3000 20 2975 30 2850 30 1600 30 1500

 步骤二（使用聚合函数去除null,得到最终结果）：

select t.deptno,max(decode(row_number,1,sal)) sal1,max(decode(row_number,2,sal)) sal2,max(decode(row_number,3,sal)) sal3 from ( select emp.*,row_number() over (partition by deptno order by sal desc) row_number, --1,2,3 rank() over (partition by deptno order by sal desc) rank, --1,1,3 dense_rank() over (partition by deptno order by sal desc) dense_rank from emp --1,1,2 ) t where t.rank<=3 group by t.deptno

 结果如下：

10 5000 2450 1300 20 3000 3000 2975 30 2850 1600 1500