原题
Write a SQL query to get the nth highest salary from the Employee table.
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.
找出表格中第n大的数字,并返回,如果没有的话,则返回null
思路
与上一次类似,使用limit来限制。(但是limit 限制中必须都是整数)
代码
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare M int;
Set M=N-1;
RETURN (
# Write your MySQL query statement below.
select distinct Salary from Employee order by Salary Desc limit M,1
);
END
如果limit中限制为N-1,则会报错,可能会出现负数,limit中限制条件必须为整数。
同时在是我写SQL语句中第一次用到声明变量这种形式,记录一下。
原题链接