177. Nth Highest Salary
Medium
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
.
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
题意
用sql求表中某一列第n大的数
思路
limit b, a等价于limit a offset b:跳过b个数,返回之后的a个数
limit/offset之后不能接表达式,只能接数字或变量,因此要先行将N-1
代码
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N-1;
RETURN (
# Write your MySQL query statement below.
SELECT
Salary
FROM
Employee
GROUP BY
Salary
ORDER BY
Salary DESC
LIMIT 1 OFFSET N
);
END