SQL> SELECT deptno, ename,2 ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC) seq3 FROM emp; DEPTNO ENAME SEQ---------- ---------- ----------10 KING 110 CLARK 210 MILLER 320 SCOTT 120 FORD 220 JONES 320 ADAMS 420 SMITH 530 BLAKE 130 ALLEN 230 TURNER 330 WARD 430 MARTIN 530 JAMES 614 rows selected.
然后你可以结合其他函数进行行和列的转换:
SQL> select deptno, 2 max(decode(seq,1,ename,null)) highest, 3 max(decode(seq,2,ename,null)) second, 4 max(decode(seq,3,ename,null)) third 5 from ( 6 select deptno,ename, 7 row_number() over 8 (partition by deptno order by sal desc) seq 9 from emp) 10 where seq <=3 group by deptno 11 /DEPTNO HIGHEST SECOND THIRD---------- ---------- ---------- ----------10 KING CLARK MILLER20 SCOTT FORD JONES30 BLAKE ALLEN TURNER
