心得

這題非常有趣，題目要求寫一預存程式輸入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    |
+----+--------+

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

   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

    

2. 這解法是由高至低排序了所有的薪水，再用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

    

參考：

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