17,086
社区成员
发帖
与我相关
我的任务
分享
select t1.*,round(t1.num/t2.all_num*100,2) || '%' 百分比
from
(select m.C# , m.Cname , (
case when n.score >= 85 then '85-100'
when n.score >= 70 and n.score < 85 then '70-85'
when n.score >= 60 and n.score < 70 then '60-70'
else '0-60'
end) as px,
count(1) num
from Course m , sc n
where m.C# = n.C#
group by m.C# , m.Cname , (
case when n.score >= 85 then '85-100'
when n.score >= 70 and n.score < 85 then '70-85'
when n.score >= 60 and n.score < 70 then '60-70'
else '0-60'
end)
order by m.C# , m.Cname , px) t1,
(select m.C# , m.Cname ,
count(1) all_num
from Course m , sc n
where m.C# = n.C#
group by m.C# , m.Cname
order by m.C# , m.Cname) t2
where t1.c#=t2.c#