You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
For example, given the above Employee table, the n th highest salary where n = 2 is 200. If there is no n th highest salary, then the query should return null.
这道题是之前那道Second Highest Salary的拓展,根据之前那道题的做法,我们可以很容易的将其推展为N,根据对Second Highest Salary中解法一的分析,我们只需要将OFFSET后面的1改为N-1就行了,但是这样MySQL会报错,估计不支持运算,那么我们可以在前面加一个SET N = N - 1,将N先变成N-1再做也是一样的:
解法一:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N - 1;
RETURN (
SELECT DISTINCT Salary FROM Employee GROUP BY Salary
ORDER BY Salary DESC LIMIT 1 OFFSET N
);
END
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
SELECT MAX(Salary) FROM Employee E1
WHERE N - 1 =
(SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2
WHERE E2.Salary > E1.Salary)
);
END
当然我们也可以通过将最后的>改为>=,这样我们就可以将N-1换成N了:
解法三:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
SELECT MAX(Salary) FROM Employee E1
WHERE N =
(SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2
WHERE E2.Salary >= E1.Salary)
);
END
Write a SQL query to get the n th highest salary from the
Employee
table.For example, given the above Employee table, the n th highest salary where n = 2 is
200
. If there is no n th highest salary, then the query should returnnull
.这道题是之前那道Second Highest Salary的拓展,根据之前那道题的做法,我们可以很容易的将其推展为N,根据对Second Highest Salary中解法一的分析,我们只需要将OFFSET后面的1改为N-1就行了,但是这样MySQL会报错,估计不支持运算,那么我们可以在前面加一个SET N = N - 1,将N先变成N-1再做也是一样的:
解法一:
根据对Second Highest Salary中解法四的分析,我们只需要将其1改为N-1即可,这里却支持N-1的计算,参见代码如下:
解法二:
当然我们也可以通过将最后的>改为>=,这样我们就可以将N-1换成N了:
解法三:
类似题目:
Second Highest Salary
参考资料:
https://leetcode.com/discuss/88875/simple-answer-with-limit-and-offset
https://leetcode.com/discuss/63183/fastest-solution-without-using-order-declaring-variables
LeetCode All in One 题目讲解汇总(持续更新中...)
The text was updated successfully, but these errors were encountered: