宠物网站建站目标,做运营常用的网站,wordpress 企业建站,企业名录查询器免费版目录 相关表数据#xff1a; 题目及思路解析#xff1a; 多表连接 1、课程编号为01且课程分数小于60#xff0c;按分数降序排列的学生信息 2、查询所有课程成绩在70分以上 的学生的姓名、课程名称和分数#xff0c;按分数升序排列 3、查询该学生不同课程的成绩…目录 相关表数据 题目及思路解析 多表连接 1、课程编号为01且课程分数小于60按分数降序排列的学生信息 2、查询所有课程成绩在70分以上 的学生的姓名、课程名称和分数按分数升序排列 3、查询该学生不同课程的成绩相同的学生编号、课程编号、学生成绩 4、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号 5、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名 6、查询学过 “李体音”老师所教的所有课的同学的学号、姓名 7、查询学过“李体音”老师所讲授的任意一门课程的学生的学号、姓名 8、查询没学过李体音老师讲授的任一门课程的学生姓名 9、查询至少有一门课与学号为“001”的学生所学课程相同的学生的学号和姓名 10、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 编辑 总结归纳 知识补充 相关表数据
Score_info表 2、Student_info表 3、Course_info表 4、Teacher_info 表 题目及思路解析
多表连接
1、课程编号为01且课程分数小于60按分数降序排列的学生信息
代码
selectst.stu_id,stu_name,birthday,gender,score
from student_info st
join score_info sc on st.stu_idsc.stu_id
where course_id01and score60
order by score desc;思路分析 这道题没什么特别的就是简单join连接查询不过结果显示可以加上成绩可能比较好看点 结果 2、查询所有课程成绩在70分以上 的学生的姓名、课程名称和分数按分数升序排列 链接 HQL,SQL刷题尚硅谷-CSDN博客 3、查询该学生不同课程的成绩相同的学生编号、课程编号、学生成绩
代码 selectsc1.stu_id,sc1.scorefrom score_info sc1inner join score_info sc2 on sc1.stu_idsc2.stu_idwhere sc1.course_id !sc2.course_id and sc1.scoresc2.score;
思路分析 这道题主要是内连接需要两张相同的表简单理解为自己连自己横向拼接两张表对比找出不同课程的然后同时成绩相同的学生成绩 结果 4、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号 注意这里隐含条件是两门课程都选修的学生 这道题目意思是在选修两门课程的学生中课程01成绩比02课程高的学生 代码1 selectsc1.stu_idfrom score_info sc1join score_info sc2 on sc1.stu_id sc2.stu_idwhere sc1.course_id01 and sc2.course_id02and sc1.scoresc2.score;
代码2
selects1.stu_id
from
(selectsc1.stu_id,sc1.course_id,sc1.scorefrom score_info sc1where sc1.course_id 01
) s1
join
(selectsc2.stu_id,sc2.course_id,scorefrom score_info sc2where sc2.course_id 02
)s2
on s1.stu_ids2.stu_id
where s1.score s2.score;
思路分析 这道题还行主要是题目开始理解错了题目意思不太明确。 代码1和代码2主要区别是代码2 join之前进行子查询 主要也是内连接自己连自己然后按照条件筛选 注意代码1比较简洁但是在大数据场景下特别是数据量比较大的时候应选择代码2 结果 5、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
代码1 selectsc1.stu_id as 学号,stu_name as 姓名from score_info sc1join score_info sc2 on sc1.stu_idsc2.stu_idleft join student_info st on st.stu_idsc1.stu_idwhere sc1.course_id 01 and sc2.course_id02;
代码2
selectt1.stu_id as 学号,s.stu_name as 姓名
from
(selectstu_idfrom score_info sc1where sc1.course_id01and stu_id in (selectstu_idfrom score_info sc2where sc2.course_id02)
)t1
join student_info s
on t1.stu_id s.stu_id;
思路分析 代码1 和代码2主要的不同在于代码1使用join连接而代码2是通过子查询 代码1是通过内连接自己连接自己然后筛选出同时选修两门课程的学生 代码2是先查询选修课程2的学生在此基础上通过stu_id连接筛选也选修了课程1的学生 注意SQL文基本没什么区别但在大数据场景下应当选择Join的即选择代码2 结果 下面三题就比较有意思了而且关联性较强 6、查询学过 “李体音”老师所教的所有课的同学的学号、姓名 HQL,SQL刷题尚硅谷-CSDN博客 7、查询学过“李体音”老师所讲授的任意一门课程的学生的学号、姓名 HQL,SQL刷题尚硅谷-CSDN博客 8、查询没学过李体音老师讲授的任一门课程的学生姓名 HQL,SQL刷题尚硅谷-CSDN博客 9、查询至少有一门课与学号为“001”的学生所学课程相同的学生的学号和姓名
代码
selectsc.stu_id,stu_name
from score_info sc
left join juntest.student_info si on sc.stu_id si.stu_id
where sc.stu_id 001 and course_id in(selectcourse_idfrom score_infowhere stu_id 001
)
group by sc.stu_id, stu_name;
思路分析 这道题比较普通先子查询得到001学生的选修课程接着在此基础上外面查询筛选课程号在001学生的选修课程之中的学生。 稍微需要注意的是子查询结果是多个因此用 in然后外面查询记得学生号不等于001即排除掉001学生 结果 10、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 注意这里的平均成绩是学生所有课程的总成绩的平均成绩 代码
selectsi.stu_name,ci.course_name,sc.score,t1.avg_score
from score_info sc
join student_info si
on sc.stu_idsi.stu_id
join course_info ci
on sc.course_idci.course_id
join
(selectstu_id,avg(score) avg_scorefrom score_infogroup by stu_id
)t1
on sc.stu_idt1.stu_id
order by t1.avg_score desc;
思路分析 这道题关键在Join子查询部分子查询部分是用来求平均成绩的如果在最外层就求平均成绩求出来的是每个科目的平均成绩因此需要加个子查询。 另外这里可能有同学觉得子查询用了score_info表Join连接又使用score_info表太过于冗余可不可以直接用子查询作为主表即from 子查询这里与上面的第二道题类似是不可以的。 结果 总结归纳 1、考察多表连接使用以及与子查询结合使用 2、在大数据场景下join连接往往意味着需要shuffle,落盘 因此Join越多计算、查询性能越差 3、在大数据场景下先子查询在Join 一方面可以减少join时候的数据量提高计算效率 另一方面是子查询可以类似spark checkpoint在执行数据计算时候出现中间数据丢 失时候可以保存计算中间临时结果 4、另外要仔细理解题目意思 知识补充 1、表示不等于符合 、! 2、在外查询与子查询连接中子查询返回结果为一个则可以使用,等若是返回多个则使用in,not in等 3、---关于sum(if())函数 sum(if()有条件累加,常用于分类筛选统计 sum(if)只试用于单个条件判断,如果筛选条件很多,我们可以用sum(case when then else end)来进行多条件筛选 注意,hive中并没有sum(distinct col1)这种使用方式,我们可以使用sum(col) group by col来达到相同效果. 4、goup by 分组聚合可以起到去重的作用
