初级练习[5]:多表查询——多表连接

目录

多表查询：表联结（续）

 课程编号为"01"且课程分数小于60，按分数降序排列的学生信息

查询所有课程成绩在70分以上的学生的姓名、课程名称和分数，按分数升序排列

 查询该学生不同课程的成绩相同的学生编号、课程编号、学生成绩

 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号

 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名

查询学过“李体音”老师所教的所有课的同学的学号、姓名

查询学过“李体音”老师所讲授的任意一门课程的学生的学号、姓名

查询没学过"李体音"老师讲授的任一门课程的学生姓名

查询至少有一门课与学号为“001”的学生所学课程相同的学生的学号和姓名

 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

---

多表查询：表联结（续）

环境准备看如下链接

环境准备https://blog.csdn.net/qq_45115959/article/details/142057624?spm=1001.2014.3001.5502

 课程编号为"01"且课程分数小于60，按分数降序排列的学生信息

hive>
select
    s.stu_id,
    s.stu_name,
    s.birthday,
    s.sex,
    t1.score
from student_info s
join (
    select
        stu_id,
        course_id,
        score
    from score_info
    where score < 60 and course_id = '01'
    ) t1
on s.stu_id=t1.stu_id
order by t1.score desc;

结果

s.stu_id        s.stu_name      s.birthday      s.sex   t1.score
017           范冰冰        1992-07-04        女      58
008           吴京          1994-02-06        男      56
007           陈坤          1999-04-09        男      48
013           蒋欣          1997-11-08        女      47
019           邓紫棋        1994-08-31        女      46
012           杨紫          1996-12-21        女      44
018           李冰冰        1993-09-24        女      38

---

查询所有课程成绩在70分以上的学生的姓名、课程名称和分数，按分数升序排列

hive>
select
    s.stu_id,
    s.stu_name,
    c.course_name,
    s2.score
from student_info s
join (
    select
        stu_id,
        sum(if(score >= 70,0,1)) flage
    from score_info
    group by stu_id
    having flage =0
    ) t1
on s.stu_id = t1.stu_id
left join score_info s2 on s.stu_id = s2.stu_id
left join course_info c on s2.course_id = c.course_id;

结果

s.stu_id      s.stu_name    c.course_name  s2.course
002     胡歌    语文    74
002     胡歌    数学    84
002     胡歌    英语    87
002     胡歌    体育    100
016     周冬雨  语文    71
016     周冬雨  数学    89
016     周冬雨  英语    71
016     周冬雨  体育    94

---

 查询该学生不同课程的成绩相同的学生编号、课程编号、学生成绩

hive>
select
    sc1.stu_id,
    sc1.course_id,
    sc1.score
from score_info sc1 
join score_info sc2 on sc1.stu_id = sc