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


--方法1


select 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 < 80 )*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 >= 80 and score < 90 )*100.0 / (select count(1) from SC wher e C# = m.C#) as decimal(18,2)) [優良率(%)],


  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 n


where m.C# = n.C#


group by m.C# , m.Cname


order by m.C#


--方法2


select 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 < 80 )*100.0 / (select count(1) from SC wher e C# = m.C#) as decimal(18,2)) [中等率(%)],


  cast((select count(1) from SC where C# = m.C# and score >= 80 and score < 90 )*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 >= 90)*100.0 / (select count(1) from SC where C# = m.C# ) as decimal(18,2)) [優秀率(%)]


from Course m


order by m.C#