*3.4.3 查询一共参加三门课程且其中一门为语文课程的学生的id和姓名

hive>

select
    t2.stu_id,
    s.stu_name
from (
         select t1.stu_id
         from (
                  select stu_id,
                         course_id
                  from score_info
                  where stu_id in (
                      select stu_id
                      from score_info
                      where course_id = "01"
                  )
              ) t1
         group by t1.stu_id
         having count(t1.course_id) = 3
     ) t2
join student_info s on t2.stu_id = s.stu_id;

结果

t2.stu_id s.stu_name006 陈道明008 吴京011 潘长江012 杨紫019 邓紫棋

第四章复杂查询

4.1 子查询

4.1.1 查询所有课程成绩均小于60分的学生的学号、姓名

hive>

select s.stu_id,
       s.stu_name
from (
         select stu_id,
                sum(if(score >= 60, 1, 0)) flag
         from score_info
         group by stu_id
         having flag = 0
     ) t1
         join student_info s on s.stu_id = t1.stu_id;

骚戴理解：这里是通过子查询加join来实现的，我一直以为这两个是不能一起用，然后主要理解下面的语句 ，首先这里是按stu_id分组，那么分组后可以看到每个学生的所有课程，然后这里的难点就是怎么判断每个课程都小于60分，这里用到了 sum(if(score >= 60, 1, 0)) ，这句是判断每门成绩是否大于60，如果大于那么返回1，小于则返回0，然后课程不止一门，这里要求每门，所以用sum去求和每门科目的返回值得和，只有每门课程的返回结果都是0，sum(if(score >= 60, 1, 0))的结果才是0，也就是flag才是0，flag为0就说明每门课程都没及格，所以最后通过 having flag = 0来获取都没有及格的

select stu_id,

sum(if(score >= 60, 1, 0)) flag

from score_info

group by stu_id

having flag = 0

结果

s.stu_id s.stu_name008 吴京017 范冰冰

*4.1.2查询没有学全所有课的学生的学号、姓名

解释：没有学全所有课，也就是该学生选修的课程数 < 总的课程数

hive>

select 
    s.stu_id,
    s.stu_name
from student_info s
left join score_info sc on s.stu_id = sc.stu_id
group by s.stu_id, s.stu_name
having count(course_id) < (select count(course_id) from course_info);

结果

s.stu_id s.stu_name

001 彭于晏

002 胡歌

003 周杰伦

004 刘德华

006 陈道明

008 吴京

010 于谦

011 潘长江

012 杨紫

013 蒋欣

014 赵丽颖

015 刘亦菲

016 周冬雨

017 范冰冰

018 李冰冰

019 邓紫棋

020 宋丹丹

4.1.3 查询出只选修了三门课程的全部学生的学号和姓名

解释：学生选修的课程数 = 3

hive>

select
    s.stu_id,
    s.stu_name
from student_info s
join (
    select
        stu_id,
        count(course_id) course_count
    from score_info
    group by stu_id
    having course_count =3
    ) t1
on s.stu_id = t1.stu_id;
骚戴解法
SELECT 
    si.stu_id,
    si.stu_name 
FROM (
    SELECT 
        stu_id 
    FROM score_info
    group by stu_id 
    HAVING COUNT(course_id)=3
)t1
join student_info si on si.stu_id = t1.stu_id;

结果

s.stu_id s.stu_name006 陈道明008 吴京011 潘长江012 杨紫019 邓紫棋

第五章多表查询

5.1 表联结

5.1.1 查询有两门及以上的课程不及格的同学的学号及其平均成绩

① 先找出有两门以上不及格的学生名单，按照学生分组，过滤组内成绩低于60的并进行count，count>=2。

② 接着做出一张表查询学生的平均成绩并和上一个子查询中的学生学号进行连接

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
join (
    select
        stu_id,
        avg(score) avg_score
    from score_info
    group by stu_id
) t2 on t1.stu_id = t2.stu_id;

骚戴理解：这里主要是学习这样的思想，把两个单独的select写出来然后再join在一起

结果

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

019 59.333333333333336

020 69.75

5.1.2 查询所有学生的学号、姓名、选课数、总成绩

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 s.stu_id = sc.stu_id
group by s.stu_id,s.stu_name;

骚戴理解：这里主要是注意分组是通过stu_id和stu_name来确定唯一的记录，很容易忘了对stu_name进行分组，还要注意group by的字段和select后面的字段要对应,例如下面的就是错的

骚戴理解：这里select后面红色画出的字段没有在group by中，所以会报错，如果select后面是聚合函数就没关系

select
    s.stu_id,
    s.stu_name,
    sc.course_id ,
    sc.score 
from student_info s
left join score_info sc on s.stu_id = sc.stu_id;

为了更好的理解，这里先做一个左连接，看看下面的执行结果，然后会发现stu_id和stu_name两个一起分组才可以确定唯一的一个组

stu_id stu_name course_id score

003 周杰伦

017 范冰冰 01 58

017 范冰冰 04 34

017 范冰冰 03 55

017 范冰冰 02 34

005 唐国强 01 64

005 唐国强 05 85

005 唐国强 04 85

005 唐国强 03 99

005 唐国强 02 44

018 李冰冰 01 38

018 李冰冰 05 87

018 李冰冰 03 49

018 李冰冰 02 58

020 宋丹丹 01 89

020 宋丹丹 04 50

020 宋丹丹 03 81

020 宋丹丹 02 59

006 陈道明 01 71

006 陈道明 03 59

006 陈道明 02 90

015 刘亦菲 01 90

015 刘亦菲 05 59

015 刘亦菲 03 84

015 刘亦菲 02 48

019 邓紫棋 01 46

019 邓紫棋 03 93

019 邓紫棋 02 39

002 胡歌 01 74

002 胡歌 04 100

002 胡歌 03 87

002 胡歌 02 84

010 于谦 01 84

010 于谦 04 34

010 于谦 03 47

010 于谦 02 68

011 潘长江 01 61

011 潘长江 03 70

011 潘长江 02 49

009 郭德纲 01 75

009 郭德纲 05 79

009 郭德纲 04 79

009 郭德纲 03 60

009 郭德纲 02 78

001 彭于晏 01 94

001 彭于晏 04 54

001 彭于晏 03 79

001 彭于晏 02 63

004 刘德华 01 85

004 刘德华 04 59

004 刘德华 03 89

004 刘德华 02 93

013 蒋欣 01 47

013 蒋欣 04 69

013 蒋欣 03 93

013 蒋欣 02 35

014 赵丽颖 01 81

014 赵丽颖 04 40

014 赵丽颖 03 32

014 赵丽颖 02 39

007 陈坤 01 48

007 陈坤 05 63

007 陈坤 04 63

007 陈坤 03 70

007 陈坤 02 55

008 吴京 01 56

008 吴京 03 39

008 吴京 02 34

012 杨紫 01 44

012 杨紫 03 62

012 杨紫 02 74

016 周冬雨 01 71

016 周冬雨 04 94

016 周冬雨 03 71

016 周冬雨 02 89

结果

stu_id stu_name course_count course_sum

001 彭于晏 4 290

002 胡歌 4 345

003 周杰伦 0 0

004 刘德华 4 326

005 唐国强 5 377

006 陈道明 3 220

007 陈坤 5 299

008 吴京 3 129

009 郭德纲 5 371

010 于谦 4 233

011 潘长江 3 180

012 杨紫 3 180

013 蒋欣 4 244

014 赵丽颖 4 192

015 刘亦菲 4 281

016 周冬雨 4 325

017 范冰冰 4 181

018 李冰冰 4 232

019 邓紫棋 3 178

020 宋丹丹 4 279

5.1.3 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

hive>

select s.stu_id,
       s.stu_name,
       avg(sc.score) avg_score
from score_info sc
left join student_info s on s.stu_id = sc.stu_id
group by s.stu_id, s.stu_name
having avg_score > 85

结果

stu_id stu_name avg_score002 胡歌 86.25

5.1.4 查询学生的选课情况：学号，姓名，课程号，课程名称

hive>

select
    s.stu_id,
    s.stu_name,
    c.course_id,
    c.course_name
from score_info sc
join course_info c on sc.course_id = c.course_id
join student_info s on sc.stu_id = s.stu_id;

骚戴理解：这里要通过score_info表来做媒介牵绳把course_info和student_info连一起

结果