Hive sql语句必练50题之41-50

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩:
select distinct a.*
from score a,score b
where a.c_id != b.c_id and a.s_score = b.s_score and a.s_id=b.s_id;

s_id c_id s_score
03 01 80
03 02 80
03 03 80

42、查询每门课程成绩最好的前三名:
select *
from(
select *,
row_number() over(distribute by c_id sort by s_score) rn
from score)a
where a.rn<=3;

s_id c_id s_score rn
06 01 31 1
04 01 50 2
02 01 70 3
04 02 30 1
02 02 60 2
03 02 80 3
04 03 20 1
06 03 34 2
03 03 80 3

43、统计每门课程的学生选修人数（超过5人的课程才统计）:
– 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select c_id,count(*) total
from score
group by c_id
having total>5
order by total desc,c_id asc;

c_id total
01 6
02 6
03 6

44、检索至少选修两门课程的学生学号:
select s_id,count(s_score) num
from score
group by s_id
having num >=2;

s_id num
01 3
02 3
03 3
04 3
05 2
06 2
07 2

45、查询选修了全部课程的学生信息:
select stu.s_id,stu.s_name
from student stu
join course cs
left join score sc on sc.s_id=stu.s_id and sc.c_id=cs.c_id
group by stu.s_id,stu.s_name
having sum((case when sc.s_score is null then 1 else 0 end)) = 0;
s_id s_name
01 赵雷
02 钱电
03 孙风
04 李云

46、查询各学生的年龄(周岁):
– 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select s_id,s_name,s_birth,(
year(current_date())-year(s_birth) -
(CASE WHEN month(current_date())>month(s_birth) THEN 0 when month(current_date())= month(s_birth) and day(current_date())>=day(s_birth) then 0 ELSE 1 END)) age
FROM student;

s_id s_name s_birth age
01 赵雷 1990-01-01 29
02 钱电 1990-12-21 28
03 孙风 1990-05-20 28
04 李云 1990-08-06 28
05 周梅 1991-12-01 27
06 吴兰 1992-03-01 27
07 郑竹 1989-07-01 29
08 王菊 1990-01-20 29

47、查询本周过生日的学生:
select *
from student
where weekofyear(current_date()) = weekofyear(s_birth);

s_id s_name s_birth s_sex

48、查询下周过生日的学生:
select *
from student
where weekofyear(current_date())+1 = weekofyear(s_birth);

s_id s_name s_birth s_sex
49、查询本月过生日的学生:
select *
from student
where month(current_date()) = month(s_birth);

s_id s_name s_birth s_sex
06 吴兰 1992-03-01 女

50、查询12月份过生日的学生:
select *
from student
where month(s_birth)=‘12’;

s_id s_name s_birth s_sex
02 钱电 1990-12-21 男
05 周梅 1991-12-01 女

51、查询性别为男的每个人的总成绩(where与having区别)
select stu.s_name,sum(s_score) sumScore
from student stu
join score sc
on stu.s_id=sc.s_id
where stu.s_sex=‘男’
group by stu.s_name
having sum(sc.s_score)>210

s_name sumscore
孙风 240
赵雷 269

select stu.s_name,sum(s_score) sumScore
from student stu
join score sc
on stu.s_id=sc.s_id
group by stu.s_sex=‘男’,stu.s_name
having sum(sc.s_score)>210