原始链接：https://blog.csdn.net/Thomson617/article/details/83281254
Hive sql语句必练50题-入门到精通(3)
承接: 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;
- 同课程成绩相同的学生的学生编号、课程编号、学生成绩:
-
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;
说明：floor((year(current_date) - year(s_birth))/4 考虑闰年的情况，减去闰年多出来的那一天。
– 47、查询本周过生日的学生:
–方法1
备注：原作者写错了，我更正了一下。
select * from student where weekofyear(CURRENT_DATE) =weekofyear(s_birth);
–方法2
备注：原作者将今天定义为10月14日，相当于写死了日期。
-
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
3819
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
