及格率 不谢 cast(cast (sum(case when res>=60 then 1 else 0 end)*100/(count(1)*1.0) as float) as nvarcha...

--18、查询各科成绩最高分、最低分和平均分：
--以如下形式显示：
-- 课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
--cast(cast (sum(case when c.score>=60 then 1 else 0 end)*100/(count(1)*1.0) as float) as nvarchar)+'%'及格率'
--查询成绩表中三科成绩的最高分 等等
select c.c#,b.cname,
max(c.score) [最高分],min(c.score) [最低分],
cast(AVG(c.score) as decimal(18,2)) [平均分],
cast(cast (sum(case when c.score>=60 then 1 else 0 end)*100/(count(1)*1.0) as float) as nvarchar)+'%' [及格率],
cast(cast (sum(case when c.score>=70 and c.score<80 then 1 else 0 end)*100/(count(1)*1.0) as float) as nvarchar)+'%' [中等率],
cast(cast (sum(case when c.score>=80 and c.score<90 then 1 else 0 end)*100/(count(1)*1.0) as float) as nvarchar)+'%' [优良率],
cast(cast (sum(case when c.score>=90 then 1 else 0 end)*100/(count(1)*1.0) as float) as nvarchar)+'%' [优秀率]
from sc c
inner join course b on c.c#=b.c#
group by c.c#,b.cname
--自己写的 完美

转载于:https://www.cnblogs.com/LiuDaohui0805/p/5149616.html