心得

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

題目

Write a SQL query to get the n^th highest salary from the Employee table.

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

1
2
3
4
5
6
7

+----+--------+
| 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.

答案

這題我是拿178. Rank Scores的解法直接套上去取N

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT a.Salary
      FROM (SELECT (@row_number:=@row_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

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT

BEGIN

RETURN (

# Write your MySQL query statement below.

SELECT a.Salary

FROM (SELECT (@row_number:=@row_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

這解法是由高至低排序了所有的薪水，再用LIMIT來獲取Nth(因為LIMIT是從0開始所以N必須先減一)，非常易懂的解法。

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

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

參考：

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

Function

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

心得

題目

答案