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;

严格模式的报错：

ERROR 1055 (42000): Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.b.s_name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因在于

：MySQL语句模式为only_full_group_by，即对于在SELECT中的列，除了采用聚合函数的列以外，其他的列必须出现在GROUP BY中，否则认为SQL语句是不合法的。

上述的MySQL语句应修改为

：

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;