六道经典SQL语句题完全掌握SQL语句(二)
O, Class, SCgrade
order by S.SNO)td_a
)td_b , C, S
where td_b.cno = C.CNO and td_b.sno = S.SNO
group by CNAME
--第5题
select c.cname "课程号",
sum(case when (scgrade<60) then 1 else 0 end) "60分以下",
sum(case when (scgrade<70 and scgrade>=60) then 1 else 0 end) "[60,70)",
sum(case when (scgrade<85 and scgrade>=70) then 1 else 0 end) "[70,85)",
sum(case when (scgrade<=100 and scgrade>=85) then 1 else 0 end) "[85,100]"
from sc,c
where sc.cno=c.cno www.2cto.com
group by c.cname
order by c.cname
--第6题
select CNAME 课程名,
max(case when ro=1 then '班级:'||td_b.Class||' 平均成绩:'||td_b.K_avg else null end) 第一,
max(case when ro=2 then '班级:'||td_b.Class||' 平均成绩:'||td_b.K_avg else null end) 第二,
max(case when ro=3 then '班级:'||td_b.Class||' 平均成绩:'||td_b.K_avg else null end) 第三
from
(
select td_a.CNO, td_a.Class, td_a.K_avg, row_number() over(partition by CNO order by K_avg desc)ro
www.2cto.com
from
(select distinct CNO, Class, avg(SCgrade)K_avg
from S inner join SC
on S.SNO = SC.SNO
group by CNO, class
order by class)td_a
)td_b, C
where td_b.CNO = C.CNO
group by CNAME
order by CNAME
--第7题
----没有max选出来有三行数据（td_e有三行数据），有了max就只有一行数据了正为所有数据
select max(case when rownum=1 then td_d.class else null end) 第一名,
max(case when rownum=2 then td_d.class else null end) 第二名,
max(case when rownum=3 then td_d.class else null end) 第三名
from
(
select td_c.class, sum1+sum2+sum3+sum4 as tscore
from
(
select td_b.class,
sum(case when ro=1 then 3 else 0 end) as sum1,
sum(case when ro=2 then 2 else 0 end) as sum2,
sum(case when ro=3 then 1 else 0 end) as sum3,
sum(case when ro<>1 and ro<>2 and ro<>3 then 0.5 else 0 end) as sum4
from www.2cto.com
(
select td_a.CNO, td_a.Class, td_a.K_avg, row_number() over(partition by CNO order by K_avg desc)ro
from
(select distinct CNO, Class, avg(SCgrade)K_avg
from S inner join SC
on S.SNO = SC.SNO
group by CNO, class
order by class)td_a
)td_b www.2cto.com
group by td_b.class
order by td_b.class
)td_c
order by tscore desc
)td_d