数据库|mysql教程
sql,查询,最高分,、最,低分,平均
数据库-mysql教程
sql 查询最高分、最低分和平均分语句
//我们要用就以学生成绩为实例吧
/*
结构
访客qq 源码,vscode失去光标,ubuntu工资,超出内存tomcat,sqlite 代码建库实例,jq插件泡泡上升动画,前端用哪种模块化框架,python 爬虫工程师面试,流行的php框架,昆明seo培训学费,挂yy网站源码,地图网页模板源码下载,web mobile 模板lzw
学生表
Student(S#,Sname,Sage,Ssex) –S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
–2.课程表
Course(C#,Cname,T#) –C# –课程编号,Cname 课程名称,T# 教师编号
个人房源店铺源码,vscode 是浏览器前端,ubuntu修复gui,tomcat的几种模式,亚马逊爬虫抢购,php语言规范,南京seo网络推广多少钱,如何提高网站收录lzw
*/
征途2脚本源码,vscode怎么java,ubuntu wen,tomcat异常500,sqlite断电数据丢失,dz插件图片本地化,ssm框架搭建后与前端,9E看看爬虫,php 日期 天数,武汉seo推广托管,企业网站资源共享,自动切换网页软件,带会员系统的网站模板lzw
查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
–及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
–方法1
select m.C# [课程编号], ame [课程名称],
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# , ame
order by m.C#
–方法2
select m.C# [课程编号], ame [课程名称],
(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#
