MySQL第二高的薪水

我就是我 2023-10-06 18:46 40阅读 0赞

#### MySQL第二高的薪水 ####

*  SQL架构
 *  题
 *  题解
 *   *  答1
     *  答2
     *  答3

## SQL架构 ##

Create table If Not Exists Employee (Id int, Salary int);
    Truncate table Employee
    insert into Employee (Id, Salary) values ('1', '100');
    insert into Employee (Id, Salary) values ('2', '200');
    insert into Employee (Id, Salary) values ('3', '300');

## 题 ##

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

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

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

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

## 题解 ##

方法一：使用子查询和 LIMIT 子句

算法

将不同的薪资按降序排序，然后使用 LIMIT 子句获得第二高的薪资。

### 答1 ###

SELECT
        (SELECT DISTINCT
                Salary
            FROM
                Employee
            ORDER BY Salary DESC
            LIMIT 1 OFFSET 1) AS SecondHighestSalary;
            
     +---------------------+
    | SecondHighestSalary |
    +---------------------+
    | 200                 |
    +---------------------+

然而，如果没有这样的第二最高工资，这个解决方案将被判断为 “错误答案”，因为本表可能只有一项记录。为了克服这个问题，我们可以将其作为临时表。

### 答2 ###

方法二：使用 IFNULL 和 LIMIT 子句

解决 “NULL” 问题的另一种方法是使用 “IFNULL” 函数，如下所示。

SELECT
        IFNULL(
          (SELECT DISTINCT Salary
           FROM Employee
           ORDER BY Salary DESC
            LIMIT 1 OFFSET 1),
        NULL) AS SecondHighestSalary;

### 答3 ###

mysql> select max(Salary) as SecondHighestSalary from Employee where Salary < (select max(Salary) from Employee);
    +---------------------+
    | SecondHighestSalary |
    +---------------------+
    |                 200 |
    +---------------------+
    1 row in set (0.00 sec)

mysql> select distinct(Salary) as SecondHighestSalary  from Employee order by Salary desc limit 1,1;
    +---------------------+
    | SecondHighestSalary |
    +---------------------+
    |                 200 |
    +---------------------+
    1 row in set (0.00 sec)