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

心得

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

題目

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.

答案

  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


這裡的資訊對您有用嗎?歡迎斗內給我