Hive sql语句必练50题-入门到精通(3)
原创Thomson617
展开
承接: Hive sql语句必练50题-入门到精通(2)
– 36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数:
select student.s_id,s_name,c_name,s_score from student
join (select sc.* from score sc
left join(select s_id from score where s_score < 70 group by s_id)tmp
on sc.s_id=tmp.s_id where tmp.s_id is null)tmp2
on student.s_id=tmp2.s_id
join course on tmp2.c_id=course.c_id
order by s_id; **-- 查询全部及格的信息**
select sc.* from score sc
left join(select s_id from score where s_score < 60 group by s_id)tmp
on sc.s_id=tmp.s_id
where tmp.s_id is null;
**-- 或(效率低)**
select sc.* from score sc
where sc.s_id not in (select s_id from score where s_score < 60 group by s_id);
– 37、查询课程不及格的学生:
select s_name,c_name as courseName,tmp.s_score
from student
join (select s_id,s_score,c_name
from score,course
where score.c_id=course.c_id and s_score < 60)tmp
on student.s_id=tmp.s_id;
–38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名:
select student.s_id,s_name,s_score as score_01
from student
join score on student.s_id=score.s_id
where c_id='01' and s_score >= 80;
– 39、求每门课程的学生人数:
select course.c_id,course.c_name,count(1)as selectNum
from course
join score on course.c_id=score.c_id
group by course.c_id,course.c_name;
– 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩:
select student.*,tmp3.c_name,tmp3.maxScore
from (select s_id,c_name,max(s_score)as maxScore from score
join (select course.c_id,c_name from course join
(select t_id,t_name from teacher where t_name='张三')tmp
on course.t_id=tmp.t_id)tmp2
on score.c_id=tmp2.c_id group by score.s_id,c_name
order by maxScore desc limit 1)tmp3
join student
on student.s_id=tmp3.s_id;
– 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩:
select distinct a.s_id,a.c_id,a.s_score from score a,score b
where a.c_id <> b.c_id and a.s_score=b.s_score;
– 42、查询每门课程成绩最好的前三名:
select tmp1.* from
(select *,row_number()over(order by s_score desc) ranking
from score where c_id ='01')tmp1
where tmp1.ranking <= 3
union all
select tmp2.* from
(select *,row_number()over(order by s_score desc) ranking
from score where c_id ='02')tmp2
where tmp2.ranking <= 3
union all
select tmp3.* from
(select *,row_number()over(order by s_score desc) ranking
from score where c_id ='03')tmp3
where tmp3.ranking <= 3;
– 43、统计每门课程的学生选修人数（超过5人的课程才统计）:
– 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select distinct course.c_id,tmp.num from course
join (select c_id,count(1) as num from score group by c_id)tmp
where tmp.num>=5 order by tmp.num desc ,course.c_id asc;
– 44、检索至少选修两门课程的学生学号:
select s_id,count(c_id) as totalCourse
from score
group by s_id
having count(c_id) >= 2;
– 45、查询选修了全部课程的学生信息:
select student.*
from student,
(select s_id,count(c_id) as totalCourse
from score group by s_id)tmp
where student.s_id=tmp.s_id and totalCourse=3;
–46、查询各学生的年龄(周岁):
– 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
方法一
select s_name,s_birth,
(year(CURRENT_DATE)-year(s_birth)-
(case when month(CURRENT_DATE) < month(s_birth) then 1
when month(CURRENT_DATE) = month(s_birth) and day(CURRENT_DATE) < day(s_birth) then 1
else 0 end)
) as age
from student;
方法二:
select s_name,s_birth,
floor((datediff(current_date,s_birth) - floor((year(current_date) - year(s_birth))/4))/365) as age
from student;
– 47、查询本周过生日的学生:
–方法1
select * from student where weekofyear(CURRENT_DATE)+1 =weekofyear(s_birth);
–方法2
select s_name,s_sex,s_birth from student
where substring(s_birth,6,2)='10'
and substring(s_birth,9,2)=14;
– 48、查询下周过生日的学生:
–方法1
select * from student where weekofyear(CURRENT_DATE)+1 =weekofyear(s_birth);
–方法2
select s_name,s_sex,s_birth from student
where substring(s_birth,6,2)='10'
and substring(s_birth,9,2)>=15
and substring(s_birth,9,2)<=21;
– 49、查询本月过生日的学生:
–方法1
select * from student where MONTH(CURRENT_DATE) =MONTH(s_birth);
–方法2
select s_name,s_sex,s_birth from student where substring(s_birth,6,2)='10';
– 50、查询12月份过生日的学生:
select s_name,s_sex,s_birth from student where substring(s_birth,6,2)='12';
所有代码亲测有效!
如果因为hive版本及测试环境造成无法运行的还请自行修正!
hive sql中的部分方法总结:
1.case when ... then ... else ... end
2.length(string)
3.cast(string as bigint)
4.rand() 返回一个0到1范围内的随机数
5.ceiling(double) 向上取整
6.substr(string A, int start, int len)
7.collect_set(col)函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段
8.concat()函数
1、功能：将多个字符串连接成一个字符串。
2、语法：concat(str1, str2,...)
返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。
9.concat_ws()函数
1、功能：和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符～（concat_ws就是concat with separator）
2、语法：concat_ws(separator, str1, str2, ...)
说明：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。
10.nvl(expr1, expr2)：空值转换函数 nvl(x,y) Returns y if x is null else return x
11.if(boolean testCondition, T valueTrue, T valueFalse)
12.row_number()over()分组排序功能,over()里头的分组以及排序的执行晚于 where group by order by 的执行。
13.获取年、月、日、小时、分钟、秒、当年第几周
select
year('2018-02-27 10:00:00') as year
,month('2018-02-27 10:00:00') as month
,day('2018-02-27 10:00:00') as day
,hour('2018-02-27 10:00:00') as hour
,minute('2018-02-27 10:00:00') as minute
,second('2018-02-27 10:00:00') as second
,weekofyear('2018-02-27 10:00:00') as weekofyear
获取当前时间:
1).current_timestamp
2).unix_timestamp()
3).from_unixtime(unix_timestamp())
4).CURRENT_DATE
