LeetCode176 & 177. Second/NtH Highest Salary - 第 2/N 高的薪水 ＜Medium＞

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

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

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

 

Write a SQL query to get the second highest salary from the Employee table.
For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.

 

第二高薪水，有以下几种思路：

1、可以对薪水进行降序排序，第二高薪水即为第二条记录 -> offset 1，限制返回一条记录 -> limit 1;

2、第二高薪水即最高薪水之下的最高薪水，即排除一个最高薪水后的序列最大值；

# Method 1
select (
    select distinct Salary from Employee order by Salary desc limit 1 offset 1
) as SecondHighestSalary 

select 
    IFNULL((select distinct Salary from Employee order by Salary desc limit 1 offset 1),NULL) 
as SecondHighestSalary

# Method 2
select Max(Salary) as SecondHighestSalary
from Employee
where Salary < (select Max(Salary) from Employee)

 

Plus ：第N高薪水解法； 注：limit 1 offset N 即跳过N个元素，向后取一个元素，等价于limit N,1

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N = N-1;
  RETURN (
      # Write your MySQL query statement below.
        select IFNULL((select distinct Salary from Employee order by Salary desc limit 1 offset N),NULL) as SecondHighestSalary 
      );
END

另解：Employee两张临时表a、b，在b表中有N-1条比a表大的数据时，a表的MAX(Salary)为第N高薪；

select MAX(Salary) as getNthHighestSalary from Employee a
where N -1 =
(select count(distinct b.Salary) from Employee b where b.Salary > a.Salary)

第二高薪即：

select MAX(Salary) as SecondHighestSalary from Employee a
where 1 =
(select count(distinct b.Salary) from Employee b where b.Salary > a.Salary)