第N高的薪水

问题描述

编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。

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

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/nth-highest-salary

思路

求第N个薪水的分页思想

• 首先先将数据去重：SELECT DISTINCT Salary FROM Employee
• 再将是数据按薪水降序排除：SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC
• 分页的思想是一页一条数据，第二高的薪水则在第二页：SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1, 1
• 第N高的薪水则在第N页:SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT N-1, 1
• 考虑到极端情况：没有第二薪水则为空，使用ifnull判断：SELECT IFNULL( (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT N-1, 1),null) AS SecondHighestSalary

作者：li-qiu-xin-yi
链接：https://leetcode-cn.com/problems/two-sum/solution/qiu-di-nge-xin-shui-de-fen-ye-si-xiang-by-li-qiu-x/

题解

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  DECLARE P INT;
  SET P = N-1;
  RETURN (  
    SELECT
    IFNULL(
    (    SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY
            Salary DESC
        LIMIT P,1),
        NULL
    ) AS getNthHighestSalary
      
  );
END