日期:2014-05-17 浏览次数:20710 次
select sal, deptno,row_number from (select emp.sal sal, emp.deptno deptno,avg(sal) over(partition by deptno) avg,row_number() over (partition by deptno order by sal desc) row_number from Scott.emp emp) order by sal_avg,row_number;
SAL DEPTNO ROW_NUMBER
---------- ---------- ----------
2850 30 1
1600 30 2
1500 30 3
1250 30 4
1250 30 5
950 30 6
3000 20 1
3000 20 2
2975 20 3
1100 20 4
800 20 5
5000 10 1
2450 10 2
1300 10 3
select sal,deptno,
row_number() over(partition by deptno order by sal desc) row_number
from emp order by avg(sal) over (partition by deptno),sal desc;
SELECT EMP.SAL SAL,
EMP.DEPTNO DEPTNO,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) ROW_NUMBER
FROM SCOTT.EMP EMP
ORDER BY AVG(SAL) OVER(PARTITION BY DEPTNO), ROW_NUMBER;