编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资,查询应该报告为 null 。
1.数据
原表:Employee
字段:id int,
salary int
2.代码:
本地mysql操作没问题
CREATE FUNCTION getNthHighestSalary(N INT)
RETURNS INT
DETERMINISTIC
BEGIN
RETURN (
SELECT MAX(NHighestSalary) AS NHighestSalary
FROM (
SELECT salary AS NHighestSalary
FROM (
SELECT salary
,ROW_NUMBER() OVER (PARTITION BY c1 ORDER BY salary DESC ) AS rn
FROM (
SELECT 1 AS c1
,salary
FROM Employee
group by 1
,salary
) o
) o1
WHERE rn = N
) o2
);
END;
select getNthHighestSalary(2);
结果1:null
结果2: 200
3.力扣上面执行却输出100,绝对是力扣上的bug