题目：

一张名为workersalary的表，要求查询出全部信息，并且salary最高的三个人按升序排列在结果的最开头，其余的人按原有顺序排列。这个sql如何写？

解答：

（1）题意理解

假如原先的表是这样的

name salary

Liu Yi 2000

Chen Er 1000

Zhang San 5000

Li Si 4000

Wang Wu 8000

Zhao Liu 6000

Sun Qi 7000

Zhou Ba 3000

题目要求变成这样

name salary

Zhao Liu 6000

Sun Qi 7000

Wang Wu 8000

Liu Yi 2000

Chen Er 1000

Zhang San 5000

Li Si 4000

Zhou Ba 3000

（2）创建表并插入数据

create table workersalary(name varchar2(10), salary number(10));

insert into workersalary values('Liu Yi',2000);

insert into workersalary values('Chen Er',1000);

insert into workersalary values('Zhang San',5000);

insert into workersalary values('Li Si',4000);

insert into workersalary values('Wang Wu',8000);

insert into workersalary values('Zhao Liu',6000);

insert into workersalary values('Sun Qi',7000);

insert into workersalary values('Zhou Ba',3000);

验证

select * from workersalary;

NAME SALARY

------------------------------ ----------

Liu Yi 2000

Chen Er 1000

Zhang San 5000

Li Si 4000

Wang Wu 8000

Zhao Liu 6000

Sun Qi 7000

Zhou Ba 3000

8 rows selected.

（3）符合题意的sql语句为

select * from

(select workersalary.*, rownum rn1, row_number() over (order by salary desc) rn2 from workersalary)

order by case when rn2<=3 then -rn2 else rn1 end;

执行后的结果为

NAME SALARY RN1 RN2

------------------------------ ---------- ---------- ----------

Zhao Liu 6000 6 3

Sun Qi 7000 7 2

Wang Wu 8000 5 1

Liu Yi 2000 1 7

Chen Er 1000 2 8

Zhang San 5000 3 4

Li Si 4000 4 5

Zhou Ba 3000 8 6

8 rows selected.

（4）分析

内查询 select workersalary.*, rownum rn1, row_number() over (order by salary desc) rn2 from workersalary得到的是按工资降序排列的纪录

NAME SALARY RN1 RN2

------------------------------ ---------- ---------- ----------

Wang Wu 8000 5 1

Sun Qi 7000 7 2

Zhao Liu 6000 6 3

Zhang San 5000 3 4

Li Si 4000 4 5

Zhou Ba 3000 8 6

Liu Yi 2000 1 7

Chen Er 1000 2 8

在此基础上，用case when rn2<=3 then -rn2 else rn1进行排序，前三个纪录进行逆序（即升序），剩余的则按rn1原来的顺序排序，即得题目中要求的结果。