create external table student(
sid string,
sname string,
ssex string,
sage int)
row format delimited fields terminated by ',';
load data local inpath '/root/test/student.txt' into table student;
create external table score(
sid string,
cid string,
sscore int)
row format delimited fields terminated by ',';
load data local inpath '/root/test/score.txt' into table score;
create external table course(
cid string,
cname string)
row format delimited fields terminated by ',';
load data local inpath '/root/test/course.txt' into table course;
需求2:查询各课的学生人数。
select b.cname, a.num
from
(select cid ,count(*) num from score group by cid) a, course b
where a.cid=b.cid;
select st.sname ,st.sid ,sum(sc.sscore)
from student st
join score sc
on st.sid=sc.sid
group by st.sname, st.sid;
需求4:求总课程平均分最高的前三名学生
select sname,b.dr,b.avgScore
from(select a.sid,avgScore,dense_rank() over(order by avgScore desc) dr
from (select sid,avg(sscore) avgScore
from score group by sid) a) b,student
where b.sid = student.sid and dr<4;