用一条sql语句查出员工工资表所有信息，工资最高的前三条信息按升序排在最前面， 其余的按原顺序排列
原始数据如下
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
答案
SQL> select (case
2 when sal_rn >= 4 then
3 0
4 else
5 sal_rn
6 end) xxxx, --只保留工资最高的三个人的排序序号，其他都修改为相同的数字0 由于后面使用降序排列，修改为0能保证其他行在下面，并且按照原来rownum排序
7 e.*
8 from (select rownum sal_rn, --按照工资降序排列的顺序
9 e.*
10 from (select rownum yuan_rn, --保留rownum及保存原顺序
11 e.* from scott.emp e order by sal desc) e) e
12 order by xxxx desc, yuan_rn
13 ;
XXXX SAL_RN YUAN_RN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ----- ---------- --------- ----- ----------- --------- --------- ------
3 3 8 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
2 2 13 7902 FORD ANALYST 7566 1981/12/3 3000.00 20
1 1 9 7839 KING PRESIDENT 1981/11/17 5000.00 10
0 14 1 7369 SMITH CLERK 7902 1980/12/17 800.00 20
0 7 2 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
0 10 3 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
0 4 4 7566 JONES MANAGER 7839 1981/4/2 2975.00 20
0 11 5 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
0 5 6 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
0 6 7 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
0 8 10 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
0 12 11 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
0 13 12 7900 JAMES CLERK 7698 1981/12/3 950.00 30
0 9 14 7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected