Hive sql语句必练50题之11-20

11、查询没有学全所有课程的同学的信息:
select distinct stu.*
from student stu
join course a
left join score b on b.s_id = stu.s_id and b.c_id = a.c_id
where b.s_score is null
;

s_id s_name s_birth s_sex
05 周梅 1991-12-01 女
06 吴兰 1992-03-01 女
07 郑竹 1989-07-01 女
08 王菊 1990-01-20 女

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息:
select distinct stu.*
from student stu
join score sc on stu.s_id = sc.s_id
where stu.s_id != ‘01’ and sc.c_id in (
select c_id
from score
where s_id = ‘01’
);

s_id s_name s_birth s_sex
02 钱电 1990-12-21 男
03 孙风 1990-05-20 男
04 李云 1990-08-06 男
05 周梅 1991-12-01 女
06 吴兰 1992-03-01 女
07 郑竹 1989-07-01 女

?13、查询和"01"号的同学学习的课程完全相同的其他同学的信息:
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;

s_id s_name s_birth s_sex course_id
01 赵雷 1990-01-01 男 01|02|03
02 钱电 1990-12-21 男 01|02|03
03 孙风 1990-05-20 男 01|02|03
04 李云 1990-08-06 男 01|02|03

14、查询没学过"张三"老师讲授的任一门课程的学生姓名:
select stu.s_name
from student stu
join teacher ter on ter.t_name=‘张三’
join course cs on ter.t_id=cs.t_id
left join score sc on cs.c_id=sc.c_id and sc.s_id=stu.s_id
where
sc.s_score is null;

s_name
吴兰
王菊

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩:
select stu.s_id,
stu.s_name,
round(avg(sc.s_score),2) avgScore
from student stu
join score sc on stu.s_id=sc.s_id
where sc.s_score<60
group by stu.s_id,stu.s_name
having count(1) >=2;

s_id s_name avgscore
04 李云 33.33
06 吴兰 32.5

16、检索"01"课程分数小于60，按分数降序排列的学生信息:
select *
from student stu
join score sc on sc.s_id = stu.s_id
where sc.c_id = ‘01’ and sc.s_score < 60
order by sc.s_score desc;

s_id s_name s_birth s_sex s_id c_id s_score
04 李云 1990-08-06 男 04 01 50
06 吴兰 1992-03-01 女 06 01 31

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩:
select *,
round(avg(s_score) over(distribute by s_id),2) avgScore
from score
order by avgScore desc,s_score desc;

s_id c_id s_score avgscore
07 03 98 93.5
07 02 89 93.5
01 03 99 89.67
01 02 90 89.67
01 01 80 89.67
05 02 87 81.5
05 01 76 81.5
03 03 80 80.0
03 02 80 80.0
03 01 80 80.0
02 03 80 70.0
02 01 70 70.0
02 02 60 70.0
04 01 50 33.33
04 02 30 33.33
04 03 20 33.33
06 03 34 32.5
06 01 31 32.5

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率:
select sc.c_id,
cs.c_name,
max(sc.s_score) maxScore,
min(sc.s_score) minScore,
round(avg(sc.s_score),2) avgScore,
round(sum(case when sc.s_score >=60 then 1 else 0 end)/count(1)*100,2) 60Score,
round(sum(case when sc.s_score between 60 and 79 then 1 else 0 end)/count(1)*100,2) 70Score,
round(sum(case when sc.s_score between 80 and 89 then 1 else 0 end)/count(1)*100,2) 80Score,
round(sum(case when sc.s_score between 90 and 100 then 1 else 0 end)/count(1)*100,2) 90Score
from score sc
join course cs on sc.c_id=cs.c_id
group by sc.c_id,cs.c_name;

c_id c_name maxscore minscore avgscor60score 70score 80score 90score
01 语文 80 31 64.5 66.67 33.33 33.33 0.0
02 数学 90 30 72.67 83.33 16.67 50.0 16.67
03 英语 99 20 68.5 66.67 0.0 33.33 33.33

19、按各科成绩进行排序，并显示排名:– row_number() over()分组排序功能
select *,
row_number() over(distribute by c_id sort by s_score desc ) rn
from score;

s_id c_id s_score rn
03 01 80 1
01 01 80 2
05 01 76 3
02 01 70 4
04 01 50 5
06 01 31 6
01 02 90 1
07 02 89 2
05 02 87 3
03 02 80 4
02 02 60 5
04 02 30 6
01 03 99 1
07 03 98 2
02 03 80 3
03 03 80 4
06 03 34 5
04 03 20 6

20、查询学生的总成绩并进行排名:
select s_id,
sum(s_score) sumScore
from score
group by s_id
order by sumScore desc;

s_id sumscore
01 269
03 240
02 210
07 187
05 163
04 100
06 65