目录
查询课程编号为03且课程成绩在80分以上的学生的学号和姓名及课程信息
多表查询:表联结示例
环境准备看如下链接文章
环境准备https://blog.csdn.net/qq_45115959/article/details/142057624?spm=1001.2014.3001.5501
查询有两门以上的课程不及格的同学的学号及其平均成绩
hive>
select
t1.stu_id,
t2.avg_score
from (
select
stu_id,
sum(if(score < 60,1,0)) flage
from score_info
group by stu_id
having flage >= 2
) t1
13join (
14 select
15 stu_id,
16 avg(score) avg_score
17 from score_info
18 group by stu_id
) t2 on t1.stu_id = t2.stu_id;
结果
t1.stu_id t2.avg_score
007 59.8
008 43.0
010 58.25
013 61.0
014 48.0
015 70.25
017 45.25
018 58.0
0019 59.333333333333336
1020 69.75
查询所有学生的学号、姓名、选课数、总成绩
hive>
select
s.stu_id,
s.stu_name,
count(sc.course_id) count_course,
sum(sc.score) sum_score
from student_info s
left join score_info sc on