Oracle之PL/SQL有關查詢實現

最近有看到網上關於排序查詢的實現有兩個自己不常用的查詢文法，關於分析函數的用法，記之，鑒之，勉之；

在一個表裡怎麼查詢每列資料的最大值和次大值？

WITH tt        AS (SELECT CASE WHEN col1 IN (1, 2) THEN empno ELSE NULL END empno,                   CASE WHEN col2 IN (1, 2) THEN ename ELSE NULL END ename,                   CASE WHEN col3 IN (1, 2) THEN job ELSE NULL END job,                   CASE WHEN col4 IN (1, 2) THEN mgr ELSE NULL END mgr,                   CASE WHEN col5 IN (1, 2) THEN hiredate ELSE NULL END                      hiredate,                   CASE WHEN col6 IN (1, 2) THEN sal ELSE NULL END sal,                   CASE WHEN col7 IN (1, 2) THEN comm ELSE NULL END comm,                   CASE WHEN col8 IN (1, 2) THEN deptno ELSE NULL END deptno              FROM (SELECT empno,                           ROW_NUMBER () OVER (ORDER BY empno DESC) col1,                           ename,                           ROW_NUMBER () OVER (ORDER BY ename DESC) col2,                           job,                           ROW_NUMBER () OVER (ORDER BY job DESC) col3,                           mgr,                           ROW_NUMBER () OVER (ORDER BY mgr DESC) col4,                           hiredate,                           ROW_NUMBER () OVER (ORDER BY hiredate DESC) col5,                           sal,                           ROW_NUMBER () OVER (ORDER BY sal DESC) col6,                           comm,                           ROW_NUMBER () OVER (ORDER BY comm DESC) col7,                           deptno,                           ROW_NUMBER () OVER (ORDER BY deptno DESC) col8                      FROM emp))SELECT MAX (empno) empno,       MAX (ename) ename,       MAX (job) job,       MAX (mgr) mgr,       MAX (hiredate) hiredate,       MAX (sal) sal,       MAX (comm) comm,       MAX (deptno) deptno  FROM ttUNION ALLSELECT MIN (empno) empno,       MIN (ename) ename,       MIN (job) job,       MIN (mgr) mgr,       MIN (hiredate) hiredate,       MIN (sal) sal,       MIN (comm) comm,       MIN (deptno) deptno  FROM tt

對一張表中的資料做如下操作啊，先對資料進行分分組，再在組內進行排序，再顯示每組中的前兩行

select * from (select d.empno,       d.deptno,       sum(sal),       rank() over(partition by deptno order by sum(sal) desc) rank  from scott.emp d group by d.empno, d.deptno) where rank<3