案例:编写一个 SQL 查询,获取 Employee 表中第 n 高的薪水(Salary),若无第N高薪水,则为null。(源自力扣题库,该提题库提交代码结果有误,以下亲测无误,以自测结果为准)
一、创建EMPLOYEE的表
create table EMPLOYEE
(
id NUMBER,
salary NUMBER
)
数据:
二、实现过程
创建自定义函数
case:当不存在第N高薪水时,查询结果为null
否则:三层select嵌套,最内层按降序排列薪水,外层查询出薪水及对应的列序号并重命名rn;最外层查询出第N 高薪水,条件为rn列序号。
注:查询结果会查出N次结果,值一样,故需要加行号数加以控制才可返回唯一值。
CREATE or replace FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER
IS
result NUMBER;
BEGIN
select case
when (select count(distinct Salary) from Employee) >N then
null
else
(select Salary
from (select Salary, rownum rn
from (select distinct Salary
from Employee
order by Salary desc))
where rn = N)
END INTO result
from Employee
where rownum <= 1;
RETURN result;
END;
三、检查执行结果
select getNthHighestSalary(2) from dual