错题收集

1、查询和”01”号的同学学习的课程完全相同的其他同学的信息:
–备注:hive不支持group_concat方法,可用 concat_ws(’|’, collect_set(str)) 实现

select student.*,tmp1.course_id from student
join (select s_id ,concat_ws('|', collect_set(c_id)) course_id from score
      group by s_id having s_id not in (1))tmp1
  on student.s_id = tmp1.s_id
join (select concat_ws('|', collect_set(c_id)) course_id2
            from score  where s_id=1)tmp2
      on tmp1.course_id = tmp2.course_id2;

本题不能有null的思路，因为基表为小表，只有基表为大表的时候left或right才能制造null
函数说明：
collect_set（指定成员）收集组内指定成员的数据；

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

select score.s_id,tmp1.s_score `语文`,tmp2.s_score `数学`,tmp3.s_score `英语`, avg(score.s_score) from score
left join
    (select s_id,s_score from score where c_id='01')tmp1 on score.s_id=tmp1.s_id
left join
    (select s_id,s_score from score where c_id='02')tmp2 on score.s_id=tmp2.s_id
left join
    (select s_id,s_score from score where c_id='03')tmp3 on score.s_id=tmp3.s_id
group by score.s_id,tmp1.s_score, tmp2.s_score, tmp3.s_score;

3、查询下周生日的人：

select * from student where weekofyear(CURRENT_DATE)+1 =weekofyear(s_birth);

4、查询没有学全所有课程的同学的信息:

select student.* from student
join (select count(c_id)num1 from course)tmp1
left join(
      select s_id,count(c_id)num2
        from score group by s_id)tmp2
on student.s_id=tmp2.s_id and tmp1.num1=tmp2.num2
where tmp2.s_id is null;