createtable emp as select*from scott.emp; altertable emp addconstraint emp_pk primarykey(empno); createtable dept as select*from scott.dept; altertable dept addconstraint dept_pk primarykey(deptno);
先看一下row_number() /rank()/dense_rank()三个函数之间的区别
select emp.deptno,emp.sal,emp.empno,row_number() over (partition by deptno orderby sal desc) row_number, --1,2,3 rank() over (partition by deptno orderby sal desc) rank, --1,1,3 dense_rank() over (partition by deptno orderby sal desc) dense_rank from emp --1,1,2
select t.deptno,t.rank,t.sal from ( select emp.*,row_number() over (partition by deptno orderby sal desc) row_number, --1,2,3 rank() over (partition by deptno orderby sal desc) rank, --1,1,3 dense_rank() over (partition by deptno orderby sal desc) dense_rank from emp --1,1,2 ) t where t.rank<=3
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 orderby sal desc) row_number, --1,2,3 rank() over (partition by deptno orderby sal desc) rank, --1,1,3 dense_rank() over (partition by deptno orderby sal desc) dense_rank from emp --1,1,2 ) t where t.rank<=3
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 orderby sal desc) row_number, --1,2,3 rank() over (partition by deptno orderby sal desc) rank, --1,1,3 dense_rank() over (partition by deptno orderby sal desc) dense_rank from emp --1,1,2 ) t where t.rank<=3 groupby t.deptno