MySQL练习题

练习题1

题目：

有一张“学生成绩表”，包含四个字段：班级id，学生id，课程id，成绩

问题1：

求出每个学生成绩最高的三条记录

求解1：

select * from (
select *, dense_rank() over (partition by 班级id,学生id order by 成绩 desc) as 顺序
from 学生成绩表 ) t1
where 顺序 <= 3;

问题2：

找出每门课程都高于班级课程平均分的学生

求解2：

可以拆解成以下几个问题：
1）求出每个班级，每门课程的平均分

select 班级id,课程id,avg(成绩) as 课程平均分
from 学生成绩表
group by 班级id,课程id;

2）将学生每门课程的成绩与所在班级的对应课程平均分相减，结果大于0就说明该学生的这门成绩高于课程平均分

将原始的“学生成绩表”与“班级的课程平均分”进行联结。
为了保持左表“学生成绩表”的所有数据，将所有学生的成绩都与“课程平均分”x相减，所以选择“左联结（left join）”。

select t1.班级id,t1.学生id,t1.课程id,t1.成绩,
       t1.成绩 - t2.课程平均分 as 相减结果
from 学生成绩表 t1
left join (
select 班级id,课程id,avg(成绩) as 课程平均分
from 学生成绩表
group by 班级id,课程id
) t2 on t1.班级id = t2.班级id and t1.课程id = t2.课程id;

3）“找出每门课程都高于班级课程平均分的学生”说明对于学生来说，最小的“相减结果”都是大于0的

select 班级id,学生id
from (
select t1.班级id,t1.学生id,t1.课程id,t1.成绩,
       t1.成绩 - t2.课程平均分 as 相减结果
from 学生成绩表 as t1
left join (
select 班级id,课程id,avg(成绩) as 课程平均分
from 学生成绩表
group by 班级id,课程id
) as t2 on t1.班级id = t2.班级id and t1.课程id = t2.课程id
) as tmp
group by 班级id,学生id
having min(相减结果) > 0;

练习题2

题目：

考虑如下的表格 salaries，编写 SQL 查询：

问题1：

添加一列以显示每个员工的部门平均工资。

求解1：

SELECT a.Employee_name, a.Department, a.Salary, b.avg_dept_sal FROM salaries AS a
INNER JOIN 
( SELECT b.Department, avg(Salary) AS avg_dept_sal FROM salaries GROUP BY Department ) AS b
WHERE a.Department = b.Department

问题2：

编写查询返回薪水第二名。

求解2：

按薪水降序排序，然后将行偏移一个，只返回一个条目；结果将是第二名的条目

SELECT Employee_name, Department, Salary FROM salaries
ORDER BY Salary DESC 
LIMIT 1
OFFSET 1

练习题3

练习题4

练习题5

练习题6