MySQL HIVE经典50题 21~25题

– 21、查询不同老师所教不同课程平均分从高到低显示

1
3个job
select distinct * from(
select t.tid,sc.cid,avg(sc.score)over(partition by t.tid,sc.cid) avg
from teacher t,course c,sc
where t.tid=c.tid and c.cid=sc.cid order by avg desc)b1
2
2个job
select t.tid,sc.cid,avg(sc.score) avg
from teacher t,course c,sc
where t.tid=c.tid and c.cid=sc.cid group by t.tid,sc.cid order by avg desc
3
2个job
select t.tid,sc.cid,avg(sc.score) avg
from teacher t join course c join sc
on t.tid=c.tid and c.cid=sc.cid group by t.tid,sc.cid order by avg desc

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

1
1个job
select *
from(
select s.*,score,
dense_rank()over(partition by cid order by score desc) rank
from sc,student s
where sc.sid=s.sid)b1 where rank=2 or rank=3

– 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
60<score<70 不生效 但能执行

select c.cid,c.cname,sc.score,
case when 85<score<100 then 1
when 70<score<85 then 2
when 60<score<70 then 3
when 0<score<60 then 4
else 5 end l1
from sc,course c where sc.cid=c.cid

select c.cid,c.cname,sc.score,
case when 85<score and score<100 then 1
when 70<score and score<85 then 2
when 60<score and score<70 then 3
when 0<score and score<60 then 4
else 5 end l1
from sc,course c where sc.cid=c.cid

答案
3个job
select distinct * from(
select cid,cname,count(b1.l1)over(partition by cid,cname,l1) a,
round(count(b1.l1)over(partition by cid,cname,l1)/c,2) b
from(
select c.cid,c.cname,sc.score,
count(1)over() c,
case when 85<score and score<100 then 1
when 70<score and score<85 then 2
when 60<score and score<70 then 3
when 0<score and score<60 then 4
else 5 end l1
from sc,course c where sc.cid=c.cid
) b1
)b2

– 24、查询学生平均成绩及其名次

3个job
select *,row_number()over(order by avg desc)
from(
select sid,round(sum/max,2) avg from(
select sid,max©over() max,sum from(
select s.sid,
count(1) c,
sum(nvl(score,0)) sum
from student s left join sc on s.sid=sc.sid
group by s.sid
)b1
)b2
)b3

– 25、查询各科成绩前三名的记录
一个job
select * from
(
select *,
row_number() over(distribute by cid sort by score desc) as rm,
rank() over(distribute by cid sort by score desc) as rk,
dense_rank() over(distribute by cid sort by score desc) as drk
from sc
) a
where a.rm < 4
;

看后续

写SQL就像写填空题
先把语法写出来
select
from
where
group
然后再根据题意加条件