Heim  >  Artikel  >  Datenbank  >  sql 查询最高分、最低分和平均分语句

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

WBOY
WBOYOriginal
2016-06-07 17:47:1116906Durchsuche

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   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 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   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#

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn