[MySQL][LeetCode][Medium] 177. Nth Highest Salary

心得

這題非常有趣,題目要求寫一預存程式輸入N並取出排名第N名的薪水為多少,這題與178\. Rank Scores差不多,所以可以直接套用。

題目

Write a SQL query to get the _n_th highest salary from the Employee table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

+----+--------+
>
> | Id | Salary |
>
> +----+--------+
>
> | 1 | 100 |
>
> | 2 | 200 |
>
> | 3 | 300 |
>
> +----+--------+
>
>

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.

答案

  1. 這題我是拿178\. Rank Scores的解法直接套上去取N
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT a.Salary
FROM (SELECT (@row_number:[email protected]_number + 1) AS Rank, z.Salary
FROM ( SELECT x.*
FROM Employee AS x
GROUP BY x.Salary
ORDER BY x.Salary DESC) AS z
JOIN (SELECT @row_number := 0) AS y) AS a
WHERE a.Rank = N
);
END

 

  1. 這解法是由高至低排序了所有的薪水,再用LIMIT來獲取Nth(因為LIMIT是從0開始所以N必須先減一),非常易懂的解法。
1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M = N - 1;
RETURN (
# Write your MySQL query statement below.
SELECT Salary
FROM Employee
GROUP BY Salary
ORDER BY Salary DESC
LIMIT M, 1
);
END

 
參考:

  1. [MySQL][LeetCode][Medium] 178. Rank Scores