关闭

mysql查找最高分最低分_sql查询最高分、最低分和平均分语句

来源:网络 文章列表 2021-05-30 8
mysql查找最高分最低分_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 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 , 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 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 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#

如您看完博文,仍不会的话,本站长提供付费技术支持服务,价格不高! 立即咨询

腾讯云限量秒杀

1核2G 5M 50元/年 2核4G 8M 74元/年 4核8G 5M 818元/年 CDN流量包 100GB 9元

版权声明

本站部分原创文章,部分文章整理自网络。如有转载的文章侵犯了您的版权,请联系站长删除处理。如果您有优质文章,欢迎发稿给我们!联系站长:
愿本站的内容能为您的学习、工作带来绵薄之力。

评论

  • 随机获取
点击刷新
精彩评论