日期:2014-05-17 浏览次数:20944 次
select empno, ename, job, sal from emp; EMPNO ENAME JOB SAL ----- ---------- --------- --------- 7369 SMITH CLERK 800.00 7499 ALLEN SALESMAN 1600.00 7521 WARD SALESMAN 1250.00 7566 JONES MANAGER 2975.00 7654 MARTIN SALESMAN 1250.00 7698 BLAKE MANAGER 2850.00 7782 CLARK MANAGER 2450.00 7788 SCOTT ANALYST 3000.00 7839 KING PRESIDENT 5000.00 7844 TURNER SALESMAN 1500.00 7876 ADAMS CLERK 1100.00 7900 JAMES CLERK 950.00 7902 FORD ANALYST 3000.00 7934 MILLER CLERK 1300.00 14 rows selected select empno, ename, job, sal from (select t.*, rownum as rn, row_number() over(order by sal desc) as rs from emp t) order by case when rs <= 3 then 0 else rn end; EMPNO ENAME JOB SAL ----- ---------- --------- --------- 7839 KING PRESIDENT 5000.00 7902 FORD ANALYST 3000.00 7788 SCOTT ANALYST 3000.00 7369 SMITH CLERK 800.00 7499 ALLEN SALESMAN 1600.00 7521 WARD SALESMAN 1250.00 7566 JONES MANAGER 2975.00 7654 MARTIN SALESMAN 1250.00 7698 BLAKE MANAGER 2850.00 7782 CLARK MANAGER 2450.00 7844 TURNER SALESMAN 1500.00 7876 ADAMS CLERK 1100.00 7900 JAMES CLERK 950.00 7934 MILLER CLERK 1300.00 14 rows selected
------解决方案--------------------
用工资倒序排序,找出前三名. 再用union all并上去掉前三的其他人.
不知道这样可以不, union all不再排序就没事
------解决方案--------------------
with tb1 as ( select 1 as id,3000 as sal from dual union all select 2 as id,2000 as sal from dual union all select 3 as id,4000 as sal from dual union all select 4 as id,5000 as sal from dual union all select 5 as id,7000 as sal from dual union all select 6 as id,6000 as sal from dual ) select id,sal from (select id,sal,row_number() over(order by sal desc) as rn from tb1) t where t.rn<=3 union all (select id,sal from tb1 minus select id,sal from (select id,sal,row_number() over(order by sal desc) as rn from tb1) t where t.rn<=3);
------解决方案--------------------