查询各科成绩最高分、最低分和平均分(case when then else end)

深渊向深渊呼唤

数据,创建表,查看数据表(代码)~

1、查询各科成绩最高分、最低分和平均分:

要求:

– 以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
– 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
– 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列

select Course.C#,Course.Cname,A.最高分,A.最低分,A.平均分,B.及格率,C.中等率,D.优良率,E.优秀率,F.选修人数
from Course 
left join (select C#, max(score) 最高分,min(score) 最低分,AVG(score) 平均分 from SC group by C#
)A on Course.C# = A.C#
left join (select C#,
(convert( decimal(5,2),( sum (case when score >= 60 then 1 else 0 end)*1.00 /count(*) ) )*100) 及格率 
from SC group by C#)B on Course.C# = B.C#
left join (select C#,
(convert( decimal(5,2),( sum (case when score >=70 and score<80 then 1 else 0 end)*1.00 /count(*) ) )*100) 中等率 
from SC group by C#)C on Course.C# = C.C#
left join (select C#,
(convert( decimal(5,2),( sum (case when score >=80 and score<90 then 1 else 0 end)*1.00 /count(*) ) )*100) 优良率 
from SC group by C#)D on Course.C# = D.C#
left join (select C#,
(convert( decimal(5,2),( sum (case when score >=90  then 1 else 0 end)*1.00 /count(*) ) )*100) 优秀率 
from SC group by C#)E on Course.C# = E.C#
left join (select C#,count(S#) 选修人数 
from SC group by C#)F on Course.C# = F.C# order by F.选修人数 desc,Course.C# asc

 
在这里插入图片描述
 

2、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比

select Course.C#,Course.Cname,A.[100-85],A.所占百分比 ,
 B.[85-70],B.所占百分比 , C.[70-60],C.所占百分比 , D.[60-0],D.所占百分比 from Course 
left join (select C#,sum (case when score >85  then 1 else 0 end) [100-85],
(convert( decimal(5,2),( sum (case when score >85  then 1 else 0 end)*1.00 /count(*) ) )*100) 所占百分比 
from SC group by C#)A on Course.C# = A.C#
left join (select C#,sum (case when score >70 and score<=85 then 1 else 0 end) [85-70],
(convert( decimal(5,2),( sum (case when score >70 and score<=85 then 1 else 0 end)*1.00 /count(*) ) )*100) 所占百分比 
from SC group by C#)B on Course.C# = B.C#
left join (select C#,sum (case when score >60 and score<=70 then 1 else 0 end) [70-60],
(convert( decimal(5,2),( sum (case when score >60 and score<=70 then 1 else 0 end)*1.00 /count(*) ) )*100) 所占百分比 
from SC group by C#)C on Course.C# = C.C#
left join (select C#,sum (case when score <= 60 then 1 else 0 end) [60-0],
(convert( decimal(5,2),( sum (case when  score <= 60 then 1 else 0 end)*1.00 /count(*) ) )*100) 所占百分比 
from SC group by C#)D on Course.C# = D.C#

 
在这里插入图片描述

栏目