LeetCode MySQL-177. Nth Highest Salary

    AllenLi
    LeetCode MySQL

    Write an SQL query to report the nth highest salary from the Employee table. If there is no nth highest salary, the query should report null.

    寫一個 SQL Query來報告員工表中第 n 個最高的售價
如果沒有，則回傳 null。

    Table: Employee

    +-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id is the primary key column for this table.
Each row of this table contains information about the salary of an employee.

    Example 1:

    Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
n = 2
Output: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

    Example 2:

    Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
n = 2
Output: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null                   |
+------------------------+

    Solution:
    1. 建立一個函式叫做 getNthHighestSalary(N INT)。
    2. 設定 N = N – 1，代表第 N 個。(搭配LIMIT OFFSET使用，原為略過 N 個數值，改成抓出第 N 個(N – 1))
    3. 篩選出在 Employee table 中，第 N 個開始取 1 個。

    Code.1:

    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N = N - 1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT(
          SELECT DISTINCT salary FROM Employee
          ORDER BY salary DESC
          LIMIT 1 OFFSET N
      )
  );
END

    Related Posts