sql 查询最高分、最低分和平均分语句

sql 查询最高分、最低分和平均分语句//我们要用就以学生成绩为实例吧/*结构学生表Student(S#,Sname,Sage,Ssex) --S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--2.课程表 Course(C#,Cname,T#) --C# --课程编号,Cname 课程名称,T# 教师编号*/查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90--方法1select m.C# [课程编号], m.Cname [课程名称], max(n.score) [最高分], min(n.score) [最低分], cast(avg(n.score) as decimal(18,2)) [平均分], cast((select count(1) from SC where C# = m.C# and score >= 60)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [及格率(%)], cast((select count(1) from SC where C# = m.C# and score >= 70 and score cast((select count(1) from SC where C# = m.C# and score >= 80 and score cast((select count(1) from SC where C# = m.C# and score >= 90)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [优秀率(%)]from Course m , SC nwhere m.C# = n.C#group by m.C# , m.Cnameorder by m.C#--方法2select m.C# [课程编号], m.Cname [课程名称], (select max(score) from SC where C# = m.C#) [最高分], (select min(score) from SC where C# = m.C#) [最低分], (select cast(avg(score) as decimal(18,2)) from SC where C# = m.C#) [平均分], cast((select count(1) from SC where C# = m.C# and score >= 60)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [及格率(%)], cast((select count(1) from SC where C# = m.C# and score >= 70 and score cast((select count(1) from SC where C# = m.C# and score >= 80 and score cast((select count(1) from SC where C# = m.C# and score >= 90)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) [优秀率(%)]from Course m order by m.C#