Fork me on GitHub

leetcode-for-sql-第N高数据

LeetCode-176-第二高的薪水

LeetCode for SQL的第二题

题目的具体描述如下:

答案

方法1

方法1思路:第二高的薪水,也就是除去最高薪水之后,在剩下的薪水中最高(自己方法);

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

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

1
2
3
4
5
6
7
8
9
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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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 行数据:

1
2
3
4
5
6
7
8
9
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 条数据

1
2
3
4
5
6
7
8
9
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 的使用之后,我们用其来实现本题中的需求:

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

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

1
2
3
4
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高的成绩和学号。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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)
1
2
3
4
5
6
7
8
9
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,这就是我们想要的结果。

1
2
3
4
5
select
s.*,
c.c_name
from Score s
left join Course c on s.c_id=c.c_id;

1
2
3
4
5
6
7
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实现翻页功能

本文标题:leetcode-for-sql-第N高数据

发布时间:2021年05月29日 - 00:05

原始链接:http://www.renpeter.cn/2021/05/29/leetcode-for-sql-%E7%AC%ACN%E9%AB%98%E6%95%B0%E6%8D%AE.html

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

Coffee or Tea