欢迎您访问365答案网,请分享给你的朋友!
生活常识 学习资料

hivesql实战50题

时间:2023-04-18
查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号(重点)

思路:将score做自连接

select *from studentwhere s_id in(select distinct  s1.s_idfrom score s1join score s2on s1.s_id = s2.s_idwhere s1.c_id = "01"and s2.c_id = "02" ands1.s_score>s2.s_score)

结果:

2.查询"01"课程比"02"课程成绩低的学生的信息及课程分数(重点):

思路:连接三张表

Select student.* ,a.s_score,b.s_score from student join score a on a.c_id = "01"join score b on b.c_id="02"Where a.s_id = student.s_id and b.s_id = student.s_id and a.s_score < b.s_score

 3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩(重点):

Select student.s_id,student.s_name,round(avg(score.s_score),1) from score join student on score.s_id = student.s_idGroup by student.s_id,student.s_nameHaving avg(score.s_score)>60

4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩:

(包括有成绩的和无成绩的)

Select student.s_id,student.s_name,round(avg(if(score.s_score==NULL,0,score.s_score)),1) from student left join score on student.s_id=score.s_idGroup by student.s_id,student.s_nameHaving avg(score.s_score)<60 or avg(score.s_score) is null

 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩:

思路:将student表和score表左连接后查出存在有成绩在score表中的置为1,无成绩在score表中的置为0,方便统计选课数目,再以此表为基础分组聚合

Select t1.sid,t1.name,sum(choice),sum(sumscore)from(Select st.s_id as sid,st.s_name as name, if(sc.s_id is null,0,1) as choice,sc.s_score sumscorefrom student st left join score sc on st.s_id = sc.s_id) t1Group by t1.sid,t1.name

 6、查询"李"姓老师的数量:

Select count(1) from teacher where t_name like '李%';

 7、查询学过"张三"老师授课的同学的信息(重点):

Select st.* from teacher tjoin course c on t.t_id = c.c_idJoin score sc on sc.c_id = c.c_idJoin student st on st.s_id = sc.s_idWhere t.t_name = '张三'

 8、查询没学过"张三"老师授课的同学的信息(重点):

思路:在先将多表连接,找出学过张三课程的s_id,再和student表进行左连接,此时匹配的存在空值的就为想要的结果

Select st.* from student st left join (Select sc.s_id s_id from teacher t join course c on t.t_id=c.t_idJoin score sc on sc.c_id = c.c_id and t.t_name = '张三') t1On st.s_id = t1.s_idWhere t1.s_id is null;

 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息(重点):

思路:将score表进行自连接,找出同时出现01和02课程的sid,再将其与student连接,找出student

Select st.* from score sc1 join score sc2 on sc1.s_id = sc2.s_idjoin student st on st.s_id = sc1.s_idWhere sc1.c_id = '01' and sc2.c_id= '02'

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息(重点):

Select stu.* from student stuJoin (select s_id from score where c_id = '01') t1on stu.s_id = t1.s_idLeft join(select s_id from score where c_id ='02') t2On Stu.s_id = t2.s_idWhere t2.s_id is null

11、查询没有学全所有课程的同学的信息(重点):

 思路:给student每一列都加上课程总数,然后再将score表中实际学生的选课数量和student做左连接,如果出现了null值,则有不满足选课条件的同学

Select stu.* from student stu Join (Select count(1) num1 from course) t1Left join (Select s_id,count(*) num2 from score group by s_id) t2On stu.s_id = t2.s_id and t1.num1 = t2.num2Where t2.s_id is null

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息(重点):

思路:先查询学号为01的同学所学课程,将其与student全匹配上,接着再将成绩表中的信息与student表进行连接,只要满足学号相等,课程号能匹配上说明选择了该课程

Select stu.* from student stujoin (Select c_id from score where s_id = '01') t1Join (select s_id,c_id from score) t2On t1.c_id = t2.c_id and stu.s_id = t2.s_idWhere stu.s_id != '01'Group by stu.s_id,stu.s_name,stu.s_birth,stu.s_sex

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息(重点):

–备注:hive不支持group_concat方法,可用 concat_ws(’|’, collect_set(str)) 实现

Select * from student stu Join (Select concat_ws( '|',collect_set(c_id)) course2 from score sc group by sc.s_id having sc.s_id = '01') t1Join (select s_id,concat_ws( '|',collect_set(c_id)) course1 from score group by score.s_id ) t2On stu.s_id = t2.s_id and t1.course2 = course1

14、查询没学过"张三"老师讲授的任一门课程的学生姓名(重点):

思路:先找出张三老师教授的课程直接与student做全匹配,此时再将student与score做左连接,如果score中出现了和全匹配表中相等的课程id,那么说明 该学生学了此课程,如果没有,则会为null

Select * from student stu Join (Select c.c_id cid from teacher t join course c on t.t_id = c.t_idWhere t.t_name = '张三') t1Left join score scOn sc.c_id = t1.cid and stu.s_id = sc .s_idWhere sc.c_id is null

15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩(重点):

思路:先查询有两门成绩不及格的同学学号,平均成绩,再和student表进行连接

Select stu.s_id,stu.s_name,t1.avgscore from student stu Join (Select s_id,avg(s_score) avgscore from score where s_score<60 group by s_id Having count(s_id)>=2) t1On stu.s_id = t1.s_id

16、检索"01"课程分数小于60,按分数降序排列的学生信息(和34类似)

Select stu.*,t1.s_score from student stu join(Select s_id,s_score from score where s_score<60 and c_id = '01') t1On stu.s_id = t1.s_idOrder by t1.s_score

 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重点):

Select sc.s_id,t1.s_score,t2.s_score,t3.s_score,avg(sc.s_score) from score sc Left join (select s_id,s_score from score where c_id = '01') t1 on sc.s_id = t1.s_idLeft join (select s_id,s_score from score where c_id = '02') t2 on sc.s_id = t2.s_idLeft join (select s_id,s_score from score where c_id = '03') t3 on sc.s_id = t3.s_idGroup by sc.s_id,t1.s_score,t2.s_score,t3.s_score

 18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率(重点):

Select sc.c_id,c.c_name,max(sc.s_score) ,min(sc.s_score),round(avg(sc.s_score),2),round(sum(case when s_score>=60 then 1 else 0 end)/count(sc.c_id),2),round(sum(case when s_score>=60 and s_score<70 then 1 else 0 end)/count(sc.c_id),2),round(sum(case when s_score>=70 and s_score<80 then 1 else 0 end)/count(sc.c_id),2),round(sum(case when s_score>=90 then 1 else 0 end)/count(sc.c_id),2)from score sc join course c on sc.c_id = c.c_idGroup by sc.c_id,c.c_name

 19、按各科成绩进行排序,并显示排名(重点row_number):

注意:重点考察窗口函数

Select score.c_id,score.s_id,score.s_score ,row_number() over(partition by c_id order by s_score desc)from score

 20、查询学生的总成绩并进行排名(重点):

Select s_id, sum(s_score),row_number() over(order by sum(s_score) desc)from score Group by s_id

21、查询不同老师所教不同课程平均分从高到低显示:

Select c_id,avg(s_score) as avgscore from score group by c_id order by avgscore

 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩:

Select t1.c_id,c1.c_name,t1.s_id,t1.s_score,t1.rk from student s1Join (Select c_id,s_id,s_score,dense_rank() over(partition by c_id order by s_score desc) rkfrom score) t1On s1.s_id = t1.s_idJoin course c1On t1.c_id = c1.c_idWhere t1.rk = 3 or t1.rk = 2

23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

Select t1.c_id,t1.bw100_85,t1.bw100_85/t5.s_sum,t2.bw85_70,t2.bw85_70/t5.s_sum,t3.bw70_60,t3.bw70_60/t5.s_sum,t4.bw60_0,t4.bw60_0/t5.s_sum from (Select c_id,sum(case when s_score<=100 and s_score>85 then 1 else 0 end) as bw100_85from scoreGroup by c_id) t1 join(Select c_id,sum(case when s_score<=85 and s_score>70 then 1 else 0 end) as bw85_70from scoreGroup by c_id) t2 on t1.c_id = t2.c_id Join (Select c_id,sum(case when s_score<=70 and s_score>65 then 1 else 0 end) as bw70_60from scoreGroup by c_id) t3 on t2.c_id = t3.c_idjoin(Select c_id,sum(case when s_score<=60 and s_score>0 then 1 else 0 end) as bw60_0from scoreGroup by c_id) t4 on t3.c_id = t4.c_id Join(Select c_id,count(*) as s_sum from score group by c_id) t5On t4.c_id = t5.c_id

24、查询学生平均成绩及其名次:

select s_id,avg(s_score) as avgscore,row_number() over() from score group by s_id order by avgscore

 

25、查询各科成绩前三名的记录

Select * from(Select c_id,s_id,s_score,row_number() over(partition by c_id order by s_score desc) as rkfrom score) t1Where t1.rk<=3

26、查询每门课程被选修的学生数:

Select c_id,count(*) from score group by c_id

 27、查询出只有两门课程的全部学生的学号和姓名:

Select s_id,count(*) as sum_course from score group by s_idhaving sum_course=2

28、查询男生、女生人数:

Select s_sex,count(*) from student group by s_sex

 

29、查询名字中含有"风"字的学生信息:

 Select * from student where s_name like "%风%";

 

 30、查询同名同性学生名单,并统计同名人数:

Select * from student s1 joinStudent s2 on s1.s_name = s2.s_name and s1.s_sex = s2.s_sex where s1.s_id != s2.s_id

 31、查询1990年出生的学生名单:

Select * from student where year(s_birth) = “1990”

 32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列:

Select c_id,avg(s_score) as avg_score from score group by c_id Order by avg_score desc,c_id

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

Select s1.s_name,t1.* from student s1 join(Select s_id,avg(s_score) as avg_score from score group by s_idhaving avg_score>=85) t1On s1.s_id = t1.s_id

 

34、查询课程名称为"数学",且分数低于60的学生姓名和分数:

select s1.s_id,s1.s_name from student s1 Join (select s_id,s_score,c_id from score where s_score<60) t1On s1.s_id = t1.s_idJoin (Select c_id from course where c_name = "数学") t2Where t2.c_id = t1.c_id

 

35、查询所有学生的课程及分数情况:

select s_id,sum(if(c_id="01",s_score,0)),sum(if(c_id="02",s_score,0)),sum(if(c_id="03",s_score,0))from scoreGroup by s_id

 36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数:

select s1.s_id,t2.c_id,t2.c_name,t1.s_score from student s1 join(Select s_id,c_id,s_score from score where s_score>70) t1On s1.s_id = t1.s_idJoin (Select c_id,c_name from course) t2On t1.c_id = t2.c_id

 37、查询课程不及格的学生:

select s1.s_id,s1.s_name from student s1 Join(Select distinct s_id from score where s_score<60) t1On s1.s_id = t1.s_id

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名: 

Select s1.s_id,s1.s_name from student s1 join(Select s_id from score where c_id = "01" and s_score>=80) t1On s1.s_id = t1.s_id

 39、求每门课程的学生人数:

Select c_id,count(*) from score group by c_id

 40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩:

Select s1.c_id,s1.s_id,s1.s_score from score s1 join (Select c_id,c1.t_id from course c1 join(Select t_id from teacher where t_name = "张三") t1On t1.t_id = c1.t_id) tt1On s1.c_id = tt1.c_idOrder by s1.s_score descLimit 1;

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

Select distinct s1.* from score s1 joinScore s2On s1.s_score = s2.s_scoreWhere s1.c_id !=s2.c_id

  42、查询每门课程成绩最好的前三名:

Select t1.c_id,t1.s_score,t1.rk from (Select c_id,s_score,row_number() over(partition by c_id order by s_score desc) as rkfrom score) t1Where t1.rk<=3

43、统计每门课程的学生选修人数(超过5人的课程才统计): – 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列

Select c_id,count(*) as rk from score group by c_id order by rk desc,c_id

44、检索至少选修两门课程的学生学号:

Select s_id,count(*) from score group by s_idHaving count(*)>=2

 45、查询选修了全部课程的学生信息:

select s_id,count(c_id) as cn from score group by s_idHaving cn in(Select count(c_id) as rn from course)

46、查询各学生的年龄(周岁): – 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一 

select s_name,if(month(current_date)

47、查询本周过生日的学生:

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

48、查询下周过生日的学生:

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

 

49、查询本月过生日的学生: 

select * from student where MonTH(CURRENT_DATE) =MonTH(s_birth);

50、查询12月份过生日的学生:

select s_name,s_sex,s_birth from student where substring(s_birth,6,2)='12';

Copyright © 2016-2020 www.365daan.com All Rights Reserved. 365答案网 版权所有 备案号:

部分内容来自互联网,版权归原作者所有,如有冒犯请联系我们,我们将在三个工作时内妥善处理。