mysql平均分sql_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#

f68f2add0b68e4f9810432fce46917b7.png

本文原创发布php中文网,转载请注明出处,感谢您的尊重!

  • 3
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值