珠江新城网站建设,网站建设ppt介绍,济南网站推广公司,wordpress 发邮件插件目录 相关表数据#xff1a; 题目及思路解析#xff1a; 查询结果排序分组指定条件 1、查询学生的总成绩并按照总成绩降序排序 2、按照如下格式显示学生的语文、数学、英语三科成绩#xff0c;没有成绩的输出为0#xff0c;按照学生的有效平均成绩降序显示 3、查询一…目录 相关表数据 题目及思路解析 查询结果排序分组指定条件 1、查询学生的总成绩并按照总成绩降序排序 2、按照如下格式显示学生的语文、数学、英语三科成绩没有成绩的输出为0按照学生的有效平均成绩降序显示 3、查询一共参加三门课程且其中一门为语文课程的学生的id和姓名 总结归纳 知识补充 相关表数据
1、student_info 2、course_info 3、score_info 题目及思路解析 查询结果排序分组指定条件
1、查询学生的总成绩并按照总成绩降序排序
代码
selectstu_id,sum(score) total_score
from score_info
group by stu_id
order by total_score desc ;
思路分析 首先进行分组获取到每位学生的所有参加考试的课程的成绩接着使用sum()函数求和按照题目要求进行输出。 注这里使用sum()不可以用count(),因为count()求的是表的行数 结果 2、按照如下格式显示学生的语文、数学、英语三科成绩没有成绩的输出为0按照学生的有效平均成绩降序显示 输出显示 学生id 语文 数学 英语 有效课程数 平均成绩 这里结果需要输出有效课程 参加考试在表中有成绩的课程数 代码
方法1 Tips: 使用sum(if())函数判断 selectstu_id,sum(if(course_name语文,score,0)) 语文,sum(if(course_name数学,score,0)) 数学,sum(if(course_name英语,score,0)) 英语,count(*) 有效课程,avg(score) 平均成绩from score_infojoin course_infoon score_info.course_idcourse_info.course_idgroup by stu_idorder by avg(score) desc;
思路 首先在score_info表中进行按照stu_id分组,得到每位学生的所有参加考试的课程的成绩接着join连接course_info表得到语文、数学、英语对应的course_id 然后使用sum(if())输出三个课程的成绩最后按照题目要求输出内容 注 count*即score_info表中的行数(即每个人有成绩的课程数) avg(score)即所有参考课程成绩的平均值 sum(if(a,b,c))即如果满足a条件则输出b(若b非空)否则输出c 方法2 Tips: 使用nvl()函数判断 selectt4.stu_id,nvl(t1.score,0) 语文,nvl(t2.score,0) 数学,nvl(t3.score,0) 英语
from
(selectstu_id,score
from score_info
where course_id01
)t1
full join(selectstu_id,score
from score_info
where course_id02
)t2
on t1.stu_idt2.stu_id
full join
(selectstu_id,score
from score_info
where course_id03
)t3
on nvl(t1.stu_id,t2.stu_id)t3.stu_id
full join
(selectstu_id,count(*) cnt,avg(score) avg_score
from score_info
group by stu_id
)t4
on coalesce(t1.stu_id,t2.stu_id,t3.stu_id)t4.stu_id;
思路 首先获取有语文有数学有英语成绩的学生以及统计课程数查询平均成绩查询 接着通过stu_id进行满外连接最后进行嵌套输出题目要求的内容 注使用满外连接的原因 因为题目要求输出三科的成绩但可能存在只有其中某一科或两科成绩的学生这样才能在最后按照要求输出三科成绩 结果 3、查询一共参加三门课程且其中一门为语文课程的学生的id和姓名
代码
selectt2.stu_id id,stu_name 姓名
from
(selectt1.stu_idfrom( selectstu_id,course_idfrom score_infowhere stu_idin (selectstu_idfrom score_infojoin course_infoon score_info.course_idcourse_info.course_idwhere course_name语文))t1group by t1.stu_idhaving count(t1.course_id)3
)t2
join student_info
on t2.stu_idstudent_info.stu_id;
思路 本题主要是嵌套查询首先查询在score_info表中有语文成绩的(即course_id为语文的course_id(需join连接course_info表获取))学生接着在上面基础上查询有成绩的课程数为3的学生最后join连接student_info表输出学生id与name 结果 总结归纳 以上题主要考察了聚合函数使用嵌套子查询分组聚合结果排序join连接的使用等 知识补充 1、在dataGrip中使用中文别名需要键盘左上角的反单引号即波浪线下面的小点 2、sum(if(a,b,c))即如果满足a条件则输出b(若b非空)否则输出c 3、 nvl(a,b)若a为空则判断b, nvl()与mysql中的 if not逻辑类似可嵌套有两个参数 coalesce(a,b,c...)相当于增强版的nvl函数与nvl不同的是coalesce可有多个参数
