大数据技术之Hive SQL题库-初级3

select
    c.course_id,
    c.course_name,
    t1.`及格人数`,
    t1.`不及格人数`
from course_info c
join (
    select
        course_id,
        sum(if(score >= 60,1,0)) as `及格人数`,
        sum(if(score < 60,1,0)) as `不及格人数`
    from score_info
    group by course_id
    ) t1 on c.course_id = t1.course_id;

select
    s.stu_id,
    s.stu_name,
    t1.score,
    t1.course_id,
    c.course_name
from student_info s
join (
    select
        stu_id,
        score,
        course_id
    from score_info
    where score > 80 and course_id = '03'
    ) t1
on s.stu_id = t1.stu_id
join course_info c on c.course_id = t1.course_id;
骚戴解法
select 
    si2.stu_id ,
    si2.stu_name,
    si.score,
    si.course_id ,
    ci.course_name 
from score_info si 
join student_info si2 on si.stu_id = si2.stu_id 
join course_info ci on si.course_id = ci.course_id 
where ci.course_id ='03' and si.score >=80; 

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;
骚戴解法
select 
    si2.stu_id ,
    si2.stu_name,
    si2.birthday,
    si2.sex,
    si.score
from score_info si 
join student_info si2 on si.stu_id = si2.stu_id 
join course_info ci on si.course_id = ci.course_id 
where ci.course_id ='01' and si.score <60
order by si.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

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;

select
    sc1.stu_id,
    sc1.course_id,
    sc1.score
from score_info sc1 
join score_info sc2 on sc1.stu_id = sc2.stu_id
where sc1.course_id <> sc2.course_id
and sc1.score = sc2.score;

select
    s1.stu_id
from
(
    select
        sc1.stu_id,
        sc1.course_id,
        sc1.score
    from  score_info sc1
    where sc1.course_id ='01'
) s1
join
(
    select
        sc2.stu_id,
        sc2.course_id,
        score
    from score_info sc2
    where sc2.course_id ="02"
)s2
on s1.stu_id=s2.stu_id
where s1.score > s2.score;

select
    t1.stu_id as `学号`,
    s.stu_name as `姓名`
from
(
    select
        stu_id
    from score_info sc1
    where sc1.course_id='01'
    and stu_id in (
          select
              stu_id
          from score_info sc2
          where sc2.course_id='02'
          )
)t1
join student_info s
on t1.stu_id = s.stu_id;

select
    t1.stu_id,
    si.stu_name
from
(
    select
        stu_id
    from score_info si
    where course_id in
    (
        select
           course_id
        from course_info c
        join teacher_info t
        on c.tea_id = t.tea_id
        where tea_name='李体音'      --李体音教的所有课程
    )
    group by stu_id
    having count(*)=2       --学习所有课程的学生
)t1
left join student_info si
on t1.stu_id=si.stu_id;
骚戴解法
select 
    si.stu_id ,
    si.stu_name 
from (
select 
    stu_id 
from score_info 
where course_id in (
    select 
        course_id 
    from course_info 
    where tea_id  in(
        select 
            tea_id 
        from teacher_info
        where tea_name = '李体音'
    )
  )
    group by stu_id
    having count(*)=2
)t 
join student_info si on si.stu_id = t.stu_id;

select
    t1.stu_id,
    si.stu_name
from
(
    select
        stu_id
    from score_info si
    where course_id in
    (
        select
           course_id
        from course_info c
        join teacher_info t
        on c.tea_id = t.tea_id
        where tea_name='李体音'
    )
)t1
left join student_info si
on t1.stu_id=si.stu_id;
骚戴解法
SELECT 
    si.stu_id ,
    si.stu_name 
FROM (
SELECT 
    stu_id 
FROM score_info 
WHERE course_id in (
    SELECT 
        course_id 
    FROM course_info 
    WHERE tea_id  in(
        SELECT 
            tea_id 
        FROM teacher_info
        WHERE tea_name = '李体音'
    )
  )
)t 
join student_info si on si.stu_id = t.stu_id;

select
    stu_id,
    stu_name
from student_info
where stu_id not in
(
    select
        stu_id
    from score_info si
    where course_id in
    (
        select
           course_id
        from course_info c
        join teacher_info t
        on c.tea_id = t.tea_id
        where tea_name='李体音'
    )
    group by stu_id
);

select
    si.stu_id,
    si.stu_name
from score_info sc
join student_info si
on sc.stu_id = si.stu_id
where sc.course_id in
(
    select
        course_id
    from score_info
    where stu_id='001'    --001的课程
) and sc.stu_id <> '001'  --排除001学生
group by si.stu_id,si.stu_name;
骚戴理解
select 
    stu_id ,
    stu_name 
from student_info 
where stu_id not in (
    select 
        stu_id 
    from score_info 
    where course_id not in (
        select 
            course_id 
        from score_info 
        where stu_id = '001'
    )
);

select
    si.stu_name,
    ci.course_name,
    sc.score,
    t1.avg_score
from score_info sc
join student_info si
on sc.stu_id=si.stu_id
join course_info ci
on sc.course_id=ci.course_id
join
(
    select
        stu_id,
        avg(score) avg_score
    from score_info
    group by stu_id
)t1
on sc.stu_id=t1.stu_id
order by t1.avg_score desc;