MySQL50-6-第16-20题
本文中介绍的是第16-20题,涉及到的知识点包含:
- 自连接
- SQL实现排序
- 多表查询
题目是:
- 检索01课程分数小于60,按分数降序排列的学生信息
- 按平均成绩从高到低(降序)显示所有学生的所有课程的成绩以及平均成绩
- 查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率;及格:>=60,中等为:70-80,优良为:80-90,优秀为:>=90
- 按照各科成绩进行排序,并且显示排名
- 查询学生的总成绩,并进行排名
题目16
题目需求
检索01课程分数小于60,按分数降序排列的学生信息
分析过程
01课程分数:Score——c_id,s_score,s_id
学生信息:Student-------s_id,s_name,s_sex,s_birth
SQL实现
自己的方法如下:
首先从Score表中找出哪些学生是满足这个要求:
1 | select |
然后直接将上面的结果和Student表查询:
1 | select s.* |
1 | select |
1 | -- 自己的方法2:两个表的直接连接查询+where条件 |
题目17
题目需求
按平均成绩从高到低(降序)显示所有学生的所有课程的成绩以及平均成绩
分析过程
1、平均成绩:Score表中按照学号分组查询
2、将上面步骤的结果和Score表在进行连接查询
SQL实现
下面是自己的解法:
1、先求出每个同学的平均分,并降序排列
1 | select |
1 | -- 自己的方法 |
1 | -- 参考方法1 |
1 | select |
严格模式的报错:
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
对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中,也就是说查出来的列必须在group by后面出现否则就会报错,或者这个字段出现在聚合函数里面。
1 | -- 参考方法2:将上面的b.s_name去掉 |
题目18
题目需求
查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率;及格:>=60,中等为:70-80,优良为:80-90,优秀为:>=90
分析过程
成绩表:Score,查询最高分、最低分和平均分
课程表:Course,课程ID,课程name
SQL实现
思路清晰:统计每个阶段的总人数,再除以总共的人数即可
将成绩表和课程表联合起来进行查询:
- case 语句用于对每个分数贴标签
- sum 语句对相应的语句中的1进行求和
1 | select |
题目19
题目需求
按照各科成绩进行排序,并且显示排名
分析过程
题目的意思是:将每科的成绩单独进行排名,类似如下的效果:
课程名 | 分数 | 排名 |
---|---|---|
英语 | 99 | 1 |
英语 | 92 | 2 |
英语 | 89 | 3 |
数学 | 88 | 1 |
数学 | 85 | 2 |
…… | …… | …… |
SQL实现
第一步:我们对Score表中的一门课程进行排名,比如01课程
1 | select * from( |
上面是针对01课程,结果为:
第二步:我们将01、02、03课程全部连接起来,通过union实现
- 表的自连接
- SQL实现排序
1 | -- 自己的方法 |
1 | -- 参考代码 |
题目20
题目需求
查询学生的总成绩,并进行排名
分析过程
- 从Score表中查出每个学生的总成绩
- 连接Student表进行排序查询
- 如何利用SQL实现排序,参考之前的文章
SQL实现
1、先查询每个学生的总成绩
1 | select |
将上面的结果和学生信息表进行关联查询:
1 | -- |
1 | -- 不使用中间表查询 |
如果想给排名加上一个排序号,参考之前的文章
1 | -- 加上排序号 |