从零开始学习hive语句

链接: 参考sql语句原文链接
建表语句参考链接原文，自行转换为hive。

查询‘01’课程比‘02’课程成绩高的学生的信息及课程分数

SELECT st.*,sc.s_score '语文',sc2.s_score '数学'
from student st 
LEFT JOIN score sc on st.s_id = sc.s_id and sc.c_id = '01' 
left join score sc2 on st.s_id = sc2.s_id and sc2.c_id = '02'
where sc.s_score > sc2.s_score;

筛选出 每门成绩都大于平均值的人(首先求出各门课程平均值，然后统计大于平均值的门数和选修课程数比较)

SELECT 
t2.s_id
from
(SELECT sc.s_id,count(sc.c_id) co,sum(case when sc.s_score > t1.avgscore then 1 else 0 END) xo
from score sc,
(SELECT c_id,avg(s_score) avgscore
from score
group by c_id) t1
where t1.c_id = sc.c_id
GROUP BY
sc.s_id)t2
where
t2.co=t2.xo;

查询’01‘课程比’02‘课程成绩低的信息

select st.*,sc.s_score,sc2.s_score 
from student st 
left join score sc on st.s_id = sc.s_id and sc.c_id = 1
left join score sc2 on st.s_id = sc2.s_id and sc2.c_id = 2
where sc.s_score < sc2.s_score;

查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select st.s_id,st.s_name,avg(sc.s_score) avgscore
from student st left join score sc
on st.s_id = sc.s_id 
group by st.s_id,st.s_name
having avgscore>60;
//或者
select st.s_id,st.s_name,avg(sc.s_score) avgscore
from student st,score sc
where st.s_id = sc.s_id 
group by st.s_id,st.s_name
having avgscore>60;

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

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

SELECT st.s_id,st.s_name,case when avg(sc.s_score) is null then 0 else avg(sc.s_score) END
from student st,score sc
where st.s_id = sc.s_id
group by st.s_id,st.s_name
having avg(sc.s_score)<60 or avg(sc.s_score) is null;

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

select st.s_id,st.s_name,count(sc.s_id),sum(case when sc.s_score is null then 0 else sc.s_score END)
from student st,score sc
where st.s_id = sc.s_id
group by st.s_id,st.s_name;

查询李姓老师的数量

select t.t_name,count(t.t_id) from teacher t
group by t.t_id,t.t_name having t.t_name like "李%";

查询张三老师授课的同学

select st.s_id,st.s_name,t.t_name
from student st,score,course,teacher t
where st.s_id=score.s_id and score.c_id=course.c_id and course.t_id = t.t_id and t.t_name='张三';

查询没有上过张三老师课的学生

select sc.s_id
from score sc,course,teacher
where teacher.t_id=course.t_id and course.c_id=sc.c_id and teacher.t_name='张三';t1

select st.s_id
from student st,t1
where
    st.s_id = t1.s_id; t2

select st2.s_id,st2.s_name
from student st2
where st2.s_id not in(select st.s_id
from student st,
(select sc.s_id
from score sc,course,teacher
where teacher.t_id=course.t_id and course.c_id=sc.c_id and teacher.t_name='张三') t1
where
    st.s_id = t1.s_id);

查询学过01和02的学生

select st.*
from student st,score sc1,score sc2
where 
    st.s_id=sc1.s_id and sc2.s_id = st.s_id and sc1.c_id='1' and sc2.c_id='2';

查询学过01 但没学02的学生

select st.*
from student st,score sc
where st.s_id=sc.s_id and sc.c_id = '1' and st.s_id not in(select sc1.s_id
from score sc1
where sc1.c_id = '2');

查询至少一门与学号01的同学所学相同的同学的信息

select st.s_id,st.s_name
from student st,score sc
where st.s_id = sc.s_id and sc.c_id in
 (select sc1.c_id from student st1,score sc1 where st1.s_id='1' and st1.s_id = sc1.s_id) 
 group by st.s_id,st.s_name;

查询和‘01’同学课程完全相同的同学（使用collect_set函数将列转行（注意参数得是string），然后将与其他同学比较）

(select concat_ws(',',collect_set(cast(sc2.c_id as string))) str2
from student st2 left join score sc2 on sc2.s_id=st2.s_id 
where st2.s_id ='1'
) ;t2

select st.s_id id,st.s_name name,concat_ws(',',collect_set(cast(sc.c_id as string))) str1
from student st,score sc
where sc.s_id=st.s_id
group by st.s_id,st.s_name;t1

select t1.id,t1.name
from (select st.s_id id,st.s_name name,concat_ws(',',collect_set(cast(sc.c_id as string))) str1
from student st,score sc
where sc.s_id=st.s_id
group by st.s_id,st.s_name)t1,(select concat_ws(',',collect_set(cast(sc2.c_id as string))) str2
from student st2 left join score sc2 on sc2.s_id=st2.s_id 
where st2.s_id ='1'
) t2
where
t1.str1=t2.str2;

查询没有学全所有课程的学生

select st.s_id id,st.s_name name,count(sc.s_id) co
from student st,score sc
where st.s_id = sc.s_id
group by st.s_id,st.s_name;t1

(select count(*)co2 from course);t2

select t1.id,t1.name
from 
(select st.s_id id,st.s_name name,count(sc.s_id) co
from student st,score sc
where st.s_id = sc.s_id
group by st.s_id,st.s_name)t1,
(select count(*)co2 from course)t2
where
t1.co< t2.co2;

查询两门以上不及格的学生

select sc.s_id,count(sc.s_score) co
from score sc
where sc.s_score < 60
group by sc.s_id;t1

select st.s_id,st.s_name
from student st,
(select sc.s_id id,count(sc.s_score) co
from score sc
where sc.s_score < 60
group by sc.s_id)t1
where t1.co >=2 and st.s_id = t1.id;

查询课程1分数小于60，按分数降序排列

select st.*,sc.s_score
from student st,score sc
where st.s_id=sc.s_id and sc.c_id = '1' and sc.s_score < 60
order by sc.s_score desc;

按平均成绩从高到低显示所有学生课程成绩及平均成绩

select st.s_id,st.s_name,
(case when sc.s_score is null then 0 else sc.s_score end) yuwen,
(case when sc2.s_score is null then 0 else sc2.s_score end) suxue,
(case when sc3.s_score is null then 0 else sc3.s_score end) yingyu,
(case when t1.avgscore is null then 0 else t1.avgscore end) avgscore
from student st
left join score sc  on sc.s_id=st.s_id  and sc.c_id="1"
left join score sc2 on sc2.s_id=st.s_id and sc2.c_id="2"
left join score sc3 on sc3.s_id=st.s_id and sc3.c_id="3"
left join (select sc.s_id,avg(sc.s_score) avgscore
from score sc
group by sc.s_id
) t1 on st.s_id = t1.s_id
order by avgscore desc;

查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率,优秀率

select c.c_id,c.c_name,sc.s_score ,sc2.s_score,sc3.s_score,t3.jige,t4.youliang
from course c
left join (select c_id,max(s_score) s_score from score group by c_id) sc on sc.c_id=c.c_id 
left join (select c_id,min(s_score) s_score from score group by c_id) sc2 on sc2.c_id=c.c_id
left join (select c_id,avg(s_score) s_score from score group by c_id) sc3 on sc3.c_id=c.c_id
left join (select t1.c_id c_id,t1.co/t2.co jige
from
(select c_id,count(s_id) co from score where s_score>=60 group by c_id )t1,
(select c_id,count(s_id) co from score group by c_id)t2
where t1.c_id = t2.c_id)t3 on t3.c_id = c.c_id
left join (select t1.c_id c_id,t1.co/t2.co youliang
from
(select c_id,count(s_id) co from score where s_score>=90 group by c_id )t1,
(select c_id,count(s_id) co from score group by c_id)t2
where t1.c_id = t2.c_id)t4 on t4.c_id = c.c_id;

按各科成绩进行排序，并显示排名

select sc.*,rank() over(partition by sc.c_id order by sc.s_score desc)
from score sc;

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

select st.s_name,t1.s_score,t1.n
from student st, (select sc.s_id id,sc.s_score s_score,row_number() over(partition by c_id order by sc.s_score desc) n
from score sc)t1
where st.s_id = t1.id and t1.n>1 and t1.n < 4;