經典SQL面試題2

題目：

一張名為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原來的順序排序，即得題目中要求的結果。