leecode地址链接
https://leetcode.com/problems/nth-highest-salary/description/
Write a SQL query to get the nth highest salary from the Employee table.
写SQL查询语句从Employee表中获取第N高的薪资
+----+--------+
| 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.
例如,给出上面的Employee表,当n=2时,第2高的薪资为200,如果没有第N高的薪资,查询结果返回null
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
Write your MySQL query statement below
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N - 1;
RETURN (
SELECT DISTINCT(Salary) FROM Employee
Union
SELECT null
ORDER BY Salary DESC LIMIT 1 OFFSET N
//或者以下语法
//select ifnull((select distinct Salary from Employee order by Salary desc limit N,1),null)
);
END
- 对比176的语法,添加了
RETURN{ }
语句,同样注意题目要求,没有值则返回null
,所以需要使用union
或者ifnull
,再使用distinct
英译’不用的’,去重。 limit
后面只能带数字常亮,使用limit n-1,1
语法错误,需要开始之前使用set
定义set n = n-1