hive sql实战50题

1. 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）

思路：将score做自连接

select *

from student

where s_id in

(select distinct  s1.s_id

from score s1

join score s2

on s1.s_id = s2.s_id

where s1.c_id = "01"

and s2.c_id = "02" and

s1.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_id
Group by student.s_id,student.s_name
Having 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_id
Group by student.s_id,student.s_name
Having 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 sumscore
from student st left join score sc on st.s_id = sc.s_id) t1
Group by t1.sid,t1.name

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

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

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

Select st.* from teacher t
join course c on t.t_id = c.c_id
Join score sc on sc.c_id = c.c_id
Join student st on st.s_id = sc.s_id
Where 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_id
Join score sc on sc.c_id = c.c_id and t.t_name = '张三') t1
On st.s_id = t1.s_id
Where  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_id
join student st on st.s_id = sc1.s_id
Where sc1.c_id =  '01' and sc2.c_id= '02'

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

Select stu.* from student stu
Join (select s_id from score where c_id = '01') t1
on stu.s_id = t1.s_id
Left join(select s_id from score where c_id ='02') t2
On Stu.s_id = t2.s_id
Where t2.s_id is null

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

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

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

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

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

Select stu.* from student stu
join (Select c_id from score where s_id = '01') t1
Join (select s_id,c_id from score) t2
On t1.c_id = t2.c_id and stu.s_id = t2.s_id
Where 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')  t1
Join (select s_id,concat_ws( '|',collect_set(c_id)) course1 from score group by score.s_id ) t2
On 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_id
Where t.t_name = '张三'
) t1
Left join score sc
On sc.c_id = t1.cid and stu.s_id = sc .s_id
Where 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) t1
On 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') t1
On stu.s_id = t1.s_id
Order 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_id
Left join (select s_id,s_score from score where c_id = '02') t2 on sc.s_id = t2.s_id
Left join (select s_id,s_score from score where c_id = '03') t3 on sc.s_id = t3.s_id
Group 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_id
Group 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 s1
Join (
Select c_id,s_id,s_score,dense_rank() over(partition by c_id order by s_score desc) rk
from score) t1
On s1.s_id = t1.s_id
Join course c1
On t1.c_id = c1.c_id
Where 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_85
from score
Group 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_70
from score
Group 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_60
from score
Group by c_id) t3 on t2.c_id = t3.c_id
join
(Select c_id,sum(case when s_score<=60 and s_score>0 then 1 else 0 end) as bw60_0
from score
Group 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) t5
On 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 rk
from score) t1
Where 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_id
having 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 join
Student 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_id
having avg_score>=85) t1
On 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)  t1
On s1.s_id = t1.s_id
Join (
Select c_id from course where c_name = "数学")  t2
Where 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 score
Group 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) t1
On s1.s_id = t1.s_id
Join (
Select c_id,c_name from course) t2
On 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) t1
On 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) t1
On 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 = "张三") t1
On t1.t_id = c1.t_id) tt1
On s1.c_id = tt1.c_id
Order by s1.s_score desc
Limit 1;

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

Select distinct s1.* from score s1 join
Score s2
On s1.s_score = s2.s_score
Where 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 rk
from score) t1
Where 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_id
Having count(*)>=2

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

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

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

select s_name,if(month(current_date)<month(s_birth),year(current_date)-year(s_birth)-1,if(day(current_date)<day(s_birth),year(CURRENT_DATE)-year(s_birth)-1,year(CURRENT_DATE)-year(s_birth))) 
from student 

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';