LeetCode-SQL-176-第二高的薪水

公众号：尤而小屋
作者：Peter
编辑：Peter

大家好，我是Peter

今天继续刷LeetCode-for-SQL的第二题：第二高的薪水。

一、题目

题目的具体描述如下：

二、答案

2.1 方法1

方法1思路：第二高的薪水，也就是除去最高薪水之后，在剩下的薪水中找最高的（自己方法）；

select 
    max(Salary) as SecondHighestSalary  -- 2、排除原数据中最高薪水之后，剩下的最大值就是第二高
from Employee 
where  Salary < (select max(Salary) from Employee);  -- 1、这个select是找到原始数据中的最高薪水

缺点：当求第二高薪水的时候，只需要嵌套一层；如果求的是第3高，那么需要将第一高、第二高的同时排除，需要排除两次

select 
 max(Salary) as ThirdHighestSalary  -- 3、确定第3高
from Employee 
where Salary < ( 
    select 
      max(Salary) as SecondHighestSalary  -- 2、找到第二高
    from Employee 
    where  Salary < (select max(Salary) from Employee);  -- 1、找到第一高
);

2.2 方法2

使用 limit 关键字来实现翻页处理，limit 的使用方法：假设现有一张 Student 表，表中全部数据如下：

mysql> select * from Student;
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 01   | 赵雷   | 1990-01-01 | 男    |
| 02   | 钱电   | 1990-12-21 | 男    |
| 03   | 孙风   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
| 05   | 周梅   | 1991-12-01 | 女    |
| 06   | 吴兰   | 1992-03-01 | 女    |
| 07   | 郑竹   | 1989-07-02 | 女    |
| 08   | 王菊   | 1990-01-20 | 女    |
+------+--------+------------+-------+
8 rows in set (0.00 sec)

1、使用limit m,n的形式：m 表示从第 m 行数据之后，不包含第 m 行，之后的 n 行数据：

mysql> select * from Student limit 2, 3;   -- 第2行之后（不包含2）的3行数据
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 03   | 孙风   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
| 05   | 周梅   | 1991-12-01 | 女    |
+------+--------+------------+-------+
3 rows in set (0.00 sec)

2、使用limit m offset n形式：表示查询结果跳过 n 条数据，读取前 m 条数据

mysql> select * from Student limit 3 offset 2;   -- 效果同上
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 03   | 孙风   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
| 05   | 周梅   | 1991-12-01 | 女    |
+------+--------+------------+-------+
3 rows in set (0.00 sec)

介绍完 limit 的使用之后，我们用其来实现本题中的需求：

select
 distinct Salary  -- 去重
from Employee
order by Salary desc   -- 薪水降序
limit 1 offset 1   -- 从第1行数据之后显示一行：除去最高的薪水之后再显示一行，也就是第二高的薪水

如果原数据中只存在一个最高值，也就说不存在第二高薪水的时候，需要显示为null，我们对上面的结果使用ifnull函数来实现：

select ifnull((select distinct Salary   -- 如果不存在则赋值为null
              from Employee
              order by Salary desc   
              limit 1 offset 1), null) as SecondHighestSalary

三、求出第n高的成绩

现在将上面的题目进行扩展，假设有两张表：Score和Course，通过字段 c_id 可以进行关联。

现在有一个需求：找出语文科目第2高的成绩和学号。

mysql> select * from Score;   -- 成绩表Score：学号s_id、课程号c_id、成绩s_score
+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
| 01   | 01   |      80 |
| 01   | 02   |      90 |
| 01   | 03   |      96 |
| 02   | 01   |      70 |
| 02   | 02   |      60 |
| 02   | 03   |      80 |
| 03   | 01   |      80 |
| 03   | 02   |      81 |
| 03   | 03   |      85 |
| 04   | 01   |      50 |
| 04   | 02   |      40 |
| 04   | 03   |      30 |
| 05   | 01   |      76 |
| 05   | 02   |      87 |
| 06   | 01   |      43 |
| 06   | 03   |      56 |
| 07   | 02   |      89 |
| 07   | 03   |      94 |
+------+------+---------+
18 rows in set (0.00 sec)

mysql> select * from Course;   -- 课程表：课程号c_id，课程名c_name，教师编号t_id
+------+--------+------+
| c_id | c_name | t_id |
+------+--------+------+
| 01   | 语文   | 02   |
| 02   | 数学   | 01   |
| 03   | 英语   | 03   |
+------+--------+------+
3 rows in set (0.00 sec)

将两张表关联起来，可以看到下面的结果，我们发现语文科目中最高的是80，第二高的是76，这就是我们想要的结果。

select 
 s.*,
 c.c_name 
from Score s 
left join Course c on s.c_id=c.c_id;

select 
 distinct s.s_score  --  分数去重
from Score s 
left join Course c on s.c_id=c.c_id
where c.c_name = '语文'  -- 指定科目
order by s.s_score desc  -- 降序
limit 1 offset 1;  -- limit和offset实现翻页功能