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

最新推荐文章于 2021-11-12 11:10:46 发布

原创最新推荐文章于 2021-11-12 11:10:46 发布 · 161 阅读

0 ·

CC 4.0 BY-SA版权

LeetCode - sql 专栏收录该内容

15 篇文章

订阅专栏

本文介绍如何使用SQL查询获取员工表中的第二高薪水，并提供多种解决方案，包括通过排序和限制查询结果的方法，以及通过查找最高薪水之下次高的薪水的方法。

编写一个 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)

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

1 条评论

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