oracle sql经典面试题,经典SQL面试题2

题目：

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

解答：

(1)题意理解

假如原先的表是这样的

namesalary

Liu

Yi2000

Chen

Er1000

Zhang

San5000

Li

Si4000

Wang

Wu8000

Zhao

Liu6000

Sun

Qi7000

Zhou

Ba3000

题目要求变成这样

namesalary

Zhao

Liu6000

Sun

Qi7000

Wang

Wu8000

Liu

Yi2000

Chen

Er1000

Zhang

San5000

Li

Si4000

Zhou

Ba3000

(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原来的顺序排序，即得题目中要求的结果。