mysql数据库sql练习总结 第三天 （复杂多表连接、专用函数式窗口）

😁 作者：Teddy (公众号：鸡仓故事汇)

⏰ 时间：2021年03月22日09:02:16

☁️ 天气：晴天

---

一、复杂多表连接

1.检索"0001"课程分数小于60，按分数降序排列的学生信息
2.查询不同老师所教不同课程平均分从高到低显示
3.查询课程名称为"数学"，且分数低于60的学生姓名和分数
4.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
5.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
6.查询课程编号为“0001”的课程比“0002”的课程成绩高的所有学生的学号
7.查询学过“孟扎扎”老师所教的所有课的同学的学号、姓名
8.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

1.检索"0001"课程分数小于60，按分数降序排列的学生信息（中等）

select st.*, s.courseid, s.achievement from student st inner join score s on st.id = s.sid where s.courseid = '0001' and s.achievement < 60 order by s.achievement DESC;

2.查询不同老师所教不同课程平均分从高到低显示（中等）

select t.id, t.name, avg(s.achievement) from teacher t inner join course c on t.id = c.tid inner join score s on c.id = s.courseid group by t.id order by avg(s.achievement) DESC;

3.查询课程名称为"数学"，且分数低于60的学生姓名和分数（中等）

# 【考察知识点】子查询
select st.name, s.achievement from student st inner join score s on st.id = s.sid where courseid = (select id from course where name = '数学') and achievement < 60;
或
select st.name, s.achievement from student st inner join score s on st.id = s.sid inner join course c on s.courseid = c.id where c.name = '数学' and s.achievement < 60;

4.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（中等）

select st.id, st.name, avg(s.achievement) from score s inner join student st on st.id = s.sid where s.achievement < 60 group by s.sid having count(s.sid) >= 2;

5.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩（中等）

select distinct s.sid, s.achievement, s.courseid from score s inner join score bs on s.sid = bs.sid where s.achievement = bs.achievement and s.courseid != bs.courseid ;

6.查询课程编号为“0001”的课程比“0002”的课程成绩高的所有学生的学号（中等）

select a.sid from (select sid, achievement from score where courseid = '0001') a
    inner join (select sid, achievement from score where courseid = '0002') b on a.sid = b.sid
    inner join student st on st.id = a.sid where a.achievement > b.achievement;

7.查询学过“孟扎扎”老师所教的所有课的同学的学号、姓名（中等）

select st.id, st.name, c.name, s.achievement, t.name from student st inner join score s on st.id = s.sid inner join course c on s.courseid = c.id inner join teacher t on c.tid = t.id where t.name = '孟扎扎';

8.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩（中等）

select s.sid,
    max(case when c.name  = '数学' then s.achievement else 0 end ) as '数学',
    max(case when c.name  = '语文' then s.achievement else 0 end ) as '语文',
    max(case when c.name  = '英语' then s.achievement else 0 end ) as '英语',
    avg(s.achievement)
from score s inner join course c on s.courseid = c.id group by s.sid;

---

二、专用函数窗口

1.查询学生平均成绩及其名次
2.按各科成绩进行排序，并显示排名
3.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
4.查询各科成绩前三名的记录（不考虑成绩并列情况）

1.查询学生平均成绩及其名次（简易）

select sid, avg(achievement), row_number() over (order by avg(achievement) DESC) from score group by sid;

2.按各科成绩进行排序，并显示排名（简易）

select courseid, row_number() over (partition by courseid order by achievement) from score;

3.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（简易）

select b.name, a.courseid, a.achievement from(
    select courseid, sid, achievement, row_number() over (partition by courseid order by achievement desc) ranking from score) a
inner join student b on a.sid = b.id where a.ranking in (2,3);

4.查询各科成绩前三名的记录（不考虑成绩并列情况）（简易）

select b.name, a.courseid, a.achievement from(
    select courseid, sid, achievement, row_number() over (partition by courseid order by achievement desc) ranking from score) a
inner join student b on a.sid = b.id where a.ranking < 4;

---

• 👉 点击调转到mysql数据库sql练习总结 第1天

ok！今天的分享就到这里，小编（Teddy）在这里先感谢大家的到来。
虽然不是太详细，小编已经很努力，给小编来个一键三连(点赞，关注，收藏)，小编会越来越努力。。。