MySQL50-7-第11-25题
本文中介绍的是第11-25题目,主要涉及的知识点是:
- 分组统计求和与百分比
- 如何利用SQL实现排序
- having使用
- union拼接
5个题目分别是
- 查询不同老师所教不同课程平均分从高到低显示
- 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
- 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
- 查询学生的平均成绩及名次
- 查询各科成绩前三名的记录
题目21
题目需求
查询不同老师所教不同课程平均分从高到低显示
分析过程
涉及到的表主要是
老师:Teacher
课程:Course,作为主表
成绩:Score
通过3个表的连接求出来即可
SQL实现
先找出每个老师教授了哪些课程:
1 | select |
将上面的结果和成绩表连接起来:
1 | select |
题目22
题目需求
查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
分析过程
成绩:Score
学生信息:Student
我们通过取出每科的第2、3名拼接起来再取出学生信息
SQL实现
自己的方法
1、课程表和成绩表连接起来,显示所有的课程和成绩信息
1 | select |
2、查出全部的语文成绩
1 | select s.s_id, s.s_score, c.c_name |
3、我们找出语文的第2、3的学生
1 | select s.s_id, s.s_score, c.c_name |
4、同时求出语文、数学、英语的分数,并且通过union
拼接
1 | -- union连接 |
5、将上面的结果学生信息表进行连接即可
好歹是实现了😭
1 | -- 最终脚本 |
和第25题相同的方法
1、以语文为例,首先我们找出前3名的成绩(包含相同的成绩)
1 | -- 语文 |
1 | -- 语文 |
在通过数学和英语的类似操作得到2、3名的成绩,再进行拼接即可
题目23
题目需求
统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
分析过程
课程:Course
成绩:Score
通过case语句来进行判断,count语句进行统计,sum语句进行求和
SQL实现
自己的方法
1、如何对每个成绩进行分组展示:ABCD代表相应的等级
1 | select |
2、将两个表关联起来展示数据
1 | -- 1、查看全部课程和成绩信息 |
3、完整代码
1 | select |
参考方法
1、先统计每个阶段的人数和占比
1 | select |
注意对比:
2、我们将4种情况同时查出来
1 | select |
3、将科目名称连接起来
1 | -- 整体和自己的方法是类似的 |
题目24
题目需求
查询学生的平均成绩及名次
分析过程
学生:Student
成绩:Score
平均:avg函数
名次:通过排序来解决
SQL实现
自己的方法
1、先求出每个人的平均分
1 | -- 自己的方法 |
2、我们对上面的结果进行排序
!!!MySQL5中是没有rank函数的,需要自己实现排序功能
1 | -- MYSQL5.7中没有rank函数,所以通过自连接实现 |
参考方法
1 | select |
实现rank函数
1 | select |
举例子来说明这个脚本:
姓名 | 成绩 |
---|---|
张三 | 89 |
李四 | 90 |
王五 | 78 |
小明 | 98 |
小红 | 60 |
- 当t1.s_score=89,满足t2.s_score > = t1.s_score的有98,90和89,此时count(distinct t2.s_score) 的个数就是3
- 当t1.s_score=90,满足t2.s_score > = t1.s_score的有98和90,此时count(distinct t2.s_score) 的个数就是2
- 当t1.s_score=78,满足t2.s_score > = t1.s_score的有98、90、89和78,此时count(distinct t2.s_score) 的个数就是4
- 当t1.s_score=98,满足t2.s_score > = t1.s_score的只有98,此时count(distinct t2.s_score) 的个数就是1
- 当t1.s_score=60,满足t2.s_score > = t1.s_score的有89、90、78、98、60,此时count(distinct t2.s_score) 的个数就是5
通过上面的步骤,我们发现:t1中每个分数对应的个数就是它的排名
题目25
题目需求
查询各科成绩前三名的记录
分析过程
这题和第22题是属于一个类型的:找到每个科目的指定名次的成绩,使用的表是:Score
SQL实现
自己的方法
1、首先我们找出语文的前3名
1 | select s.s_id, s.s_score, c.c_name |
2、通过同样的方法我们可以求出数学和英语的前3条记录,然后通过union进行联结,有待优化😭
1 | -- 自己的脚本 |
参考方法
通过Score表的自连接,表a中的值小于表b中的值,排序之后我们取前3
1 | select |
我们通过语文
这个科目来理解上面的代码:前3名是80,80,76
1 | -- 语文 |
1 | -- 语文 |