MySQL 基本查询15--20
# 题目15:LeetCode-for-SQL的第二题:第二高的薪水
编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary) 。
例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null。
方法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、找到第一高
);
方法1:使用 limit 关键字来实现翻页处理 1)使用limit m,n的形式:m 表示从第 m 行数据之后,不包含第 m 行,之后的 n 行数据 2)使用limit m offset n形式:表示查询结果跳过 n 条数据,读取前 m 条数据
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
IFNULL() 函数用于判断第一个表达式是否为 NULL,如果为 NULL 则返回第二个参数的值,如果不为 NULL 则返回第一个参数的值
# 题目16:求出第n高的成绩(找出语文科目第2高的成绩和学号)
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实现翻页功能
# 题目17:LeetCode-SQL-596-超过5名学生的课程
有一个 courses 表 ,有 student (学生) 和 class (课程)。请列出所有超过或等于5名学生的课。例如,表: 在最下面有个提示:学生在每个课中不应被重复计算。即如果A同学重修了Math课程,则不应该被计算在内。
-- 方法1
select
class
from courses
group by class
having count(distinct student) >=5; -- distinct去重关键
-- 方法2
select
class
from(
select
distinct *
from courses) t -- 临时表
group by class
having count(class) >= 5;
本题中最大的陷阱就是有重修课程的同学,但是给出的数据中没有展现出来,所以上面的方法中都会出现去重的操作
# 题目18:LeetCode-SQL-181-超过经理收入的员工
Employee 表包含所有员工,他们的经理也属于员工。每个员工都有一个 Id,此外还有一列对应员工的经理的 Id。
| Id | Name | Salary | ManagerId |
|---|---|---|---|
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
| - | - | - | - |
给定 Employee 表,编写一个 SQL 查询,该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中,Joe 是唯一一个收入超过他的经理的员工。
|-|
| Employee |
|---|
| Joe |
| - |
题目利用如下的图形解释:Joe是员工,工资是70000,经理是编号3,也就是Sam,但是Sam工资只有60000
-- 方法1:自连接
select
e1.Name as Employee
from Employee e1 -- 表的自连接
left join Employee e2
on e1.ManagerId = e2.Id -- 连接条件
where e1.Salary > e2.Salary
-- 方法2:where条件过滤
select
a.Name as Employee
from Employee as a,Employee as b -
where a.ManagerId = b.Id
and a.Salary > b.Salary;
# 题目19:检索01课程分数小于60,按分数降序排列的学生信息
SELECT
t1.s_id
,t1.s_name
,t1.s_birth
,t1.s_sex
,t2.s_score
FROM
student t1
JOIN score t2 ON t1.s_id = t2.s_id
WHERE
t2.c_id = 01
AND t2.s_score < 60
ORDER BY t2.s_score DESC;
# 题目20:按平均成绩从高到低(降序)显示所有学生的所有课程的成绩以及平均成绩
-- 方法1
SELECT
t1.s_id,
t1.c_id,
t1.s_score,
t2.avg_score
FROM
score t1
JOIN ( -- 创建临时表,并进行连接
SELECT
s_id,
ROUND(AVG(s_score), 2) avg_score
FROM
score
GROUP BY
s_id
) t2 ON t1.s_id = t2.s_id
ORDER BY
4 DESC; -- 按照select中的第4列进行降序排列
为何采用聚合函数MAX:
-- 方法2:采用case when then end语句
SELECT
s.s_id,
MAX(CASE s.c_id WHEN '01' THEN s.s_score END) 语文,
MAX(CASE s.c_id WHEN '02' THEN s.s_score END) 数学,
MAX(CASE s.c_id WHEN '03' THEN s.s_score END) 英语,
ROUND(AVG(s.s_score), 2) avg_score
FROM
score s
GROUP BY
s.s_id
ORDER BY
5 DESC;
注意的点:对于下面的例子而言
select
s.s_id
,max(case s.c_id when '01' then s.s_score end) 语文
,max(case s.c_id when '02' then s.s_score end) 数学
,max(case s.c_id when '03' then s.s_score end) 英语
,avg(s.s_score)
,b.s_name -- 没有出现在group by子句中,导致报错
from score s
join Student b
on s.s_id = b.s_id
group by s.s_id
order by 5 desc;
select
s.s_id
,max(case s.c_id when '01' then s.s_score end) 语文 -- 由于有些数据是空值,所以必须采用聚合函数,才能将真实值取出
,max(case s.c_id when '02' then s.s_score end) 数学
,max(case s.c_id when '03' then s.s_score end) 英语
,avg(s.s_score)
,b.s_name -- 没有出现在group by子句中,导致报错
from score s
join Student b
on s.s_id = b.s_id
group by s.s_id, b.s_name
order by 5 desc;