mysql求及格百分率_关于mysql进⾏名次的排名和计算及格率
的分享
关于mysql进⾏学⽣成绩名次和及格率的计算
前⾔：最近在学习mysql，然后到了⼀些⾯试题进⾏了练习，发现了⼏个有趣的⾯试题，在这⾥特地分享给⼤家，有如何计算名次以及及格率
⼀、mysql计算成绩排名
前提：以下是样例表的结构
1 Student(S_,Sname,Sage,Ssex) 学⽣表
2 S_：学⽣编号，字符串型
3 Sname： 学⽣姓名，字符串型
4 Sage：年龄 ，数值型
5 Ssex：性别，字符串型6
7 Course(C_,Cname,T_) 课程表8 C_：课程编号 ，字符串型 (企业管理001，马列002，UML 003，数据库004，英语006)9 Cname：课程名称 ，字符串型10 T_：教师编号，字符串型11
12 SC(S_,C_,score) 成绩表13 S_：学⽣编号 ，字符串型14 C_：课程编号 ，字符串型15 score：成绩，数值型16
17 Teacher(T_,Tname) 教师表18 T_：教师编号 ，字符串型19 Tname：教师姓名，字符串型
(⼀) 代码演⽰
1 SELECT
2 1+(
3 SELECT
4 COUNT( DISTINCT平均成绩 )
5 FROM
6 ( SELECT S_, AVG( score ) AS 平均成绩 FROM SC GROUP BY S_ ) AST1
7 WHERE
8 平均成绩 >T2.平均成绩9 ) AS名次,10 S_ AS学⽣学号,11 平均成绩12 FROM
13 ( SELECT S_, AVG( score ) 平均成绩 FROM SC GROUP BY S_ ) AST214 ORDER BY法语que的用法
15 平均成绩 DESC;mysql面试题基础知识
(⼆) 结果演⽰
1 +------+----------+-----------+
2 | 名次 | 学⽣学号 | 平均成绩 |
3 +------+----------+-----------+
4 | 1 | 100
5 | 99.000000 |
5 | 2 | 1010 | 85.500000 |
6 | 3 | 1003 | 84.250000 |
7 | 4 | 1004 | 75.000000 |
8 | 5 | 1007 | 71.333333 |
9 | 6 | 1006 | 68.250000 |
10 | 7 | 1012 | 66.000000 |
11 | 8 | 1011 | 57.750000 |
12 | 9 | 1001 | 56.000000 |
13 | 10 | 1002 | 47.500000 |
14 | 11 | 1008 | 44.000000 |
15 | 12 | 1009 | 34.000000 |
16 +------+----------+-----------+
17 12 rows in set (0.06 sec)
(三) 内容讲解
上述的查询语句中运⽤了很多技巧，但是我们只挑⼏个重点进⾏讲解，⾄于相关⾃查询就没有讲解，以及distinct也没有讲解，distinct的讲解这⾥推荐⼀篇博⽂。
A n+的使⽤
n+放在()⼦查询的前⾯，这⾥的n是表明指定的字段的数值是从n开始的，如果是1就是从1开始，2就是从2开始。然后+的意思就是该字段的值从n开始每次都+1，这⾥和Java⾃增++的使⽤有些神似，当然这是这两个完全没有关系。这⾥是1+那么就是从1开始每个值加1，⽅向取决于如何排序和⽤什么字段进⾏排序。
B 排序算法的讲解
简单解释下这个查询运⽤到的排序算法，⾸先按照学⽣进⾏分组，之后运⽤了相关⼦查询同时使⽤了distinct关键字去除了完全相同的列。将平均成绩和相同表格的平均成绩进⾏⼀⼀⽐较，对于当前平均成绩⽐其他平均成绩⾼的⾏记录进⾏了计数。以表1为例，就是学号1001的平均成绩和1002-1012进⾏了⽐较，如果该成绩⼤于其他平均成绩就+1。这时每位同学都⽐较完了，那么得到了⼀个以学⽣编号
为分组的⽽且记录了平均成绩⾼于其他同学的平均成绩的次数表2。此时的表2的开始值是0，因此此时1+就派上了⽤场，⽤初始值变成了1，那么接下来对其进⾏排序就可以得到名次表，就如以上的结果演⽰表。
注：表格分解
表1：平均成绩表
1 +------+-----------+
2 | S_ | 平均成绩 |
3 +------+-----------+
4 | 1001 | 56.000000 |
5 | 1002 | 47.500000 |
6 | 1003 | 84.250000 |
7 | 1004 | 75.000000 |
8 | 1005 | 99.000000 |
9 | 1006 | 68.250000 |
10 | 1007 | 71.333333 |
11 | 1008 | 44.000000 |
12 | 1009 | 34.000000 |
13 | 1010 | 85.500000 |
14 | 1011 | 57.750000 |
15 | 1012 | 66.000000 |
16 +------+-----------+
表2：⾼于其他成绩次数表
1 +------+------+
2 | 学号 | 次数 |
3 +------+------+
4 | 1001 | 8 |
5 | 1002 | 9 |
6 | 1003 | 2 |
7 | 1004 | 3 |
8 | 1005 | 0 |
9 | 1006 | 5 |
10 | 1007 | 4 |
11 | 1008 | 10 |
12 | 1009 | 11 |
13 | 1010 | 1 |
14 | 1011 | 7 |
15 | 1012 | 6 |
16 +------+------+
⼆、mysql计算及格率
(⼀) 代码演⽰
1 SELECT
2 t.C_ AS课程号,
长整型的取值范围sql server 2008有多大3 max( course.Cname ) AS课程名,
怎么安装selenium库4 ifnull( AVG( score ), 0 ) AS平均成绩,
5 100 * SUM( CASE WHEN ifnull( score, 0 ) >= 60 THEN 1 ELSE 0 END ) / COUNT( * ) AS及格百分数
6 FROM
7 SC T,8 Course9 WHERE
10 t.C_ =course.C_11 GROUP BY
12 t.C_13 ORDER BY
14 ifnull(avg(score),0),100 * SUM( CASE WHEN ifnull( score, 0 ) >= 60 THEN 1 ELSE 0 END ) / COUNT( * ) DESC;
(⼆) 结果演⽰
1 +--------+----------+-----------+------------+
2 | 课程号 | 课程名 | 平均成绩 | 及格百分数 |
3 +--------+----------+-----------+------------+
sumif函数怎么用套用4 | 004 | 数据库 | 48.200000 | 40.0000 |
5 | 001 | 企业管理 | 59.142857 | 57.1429 |
6 | 002 | 马列 | 65.400000 | 60.0000 |
7 | 006 | 英语 | 67.750000 | 75.0000 |
8 | 003 | UML | 84.750000 | 100.0000 |
9 +--------+----------+-----------+------------+
(三) 内容讲解
其实这段代码也没有什么可以解释的，就是⼀些函数的和选择结构的运⽤
A ifnull()函数的使⽤
这⾥的ifnull的作⽤是为了保证成绩不为空，其实就是将为空的成绩变成0，⽅便计算。ifnull函数的⽤法是ifnull(字段，值(如果前⾯的字段值是null就返回此值))，推荐⼀个博客讲述该函数⽤法。其实ifnull也可以⽤coalesce()函数进⾏替代，他们的⽤法是完全⼀样的，或者使⽤if(表达式1，值1，值2)。
B case嵌套在聚集函数sum()
这⾥确实是⼀个新的知识点，⼀般我们很少在聚集函数中去嵌套选择结构或者语法，这⾥却⽤到了这
样的⽅法让⼈⽿⽬⼀新。这⾥的case语句的作⽤是使得⼤于60的成绩为1和⼩于60为0，为了⽅便sum函数统计及格⼈数。值得注意的⼀点是这⾥case的⽤法和平时的语法不⼀样，⼀般在⾮⾃定义函数或者存储过程中的case⽤法是(case 字段 when 条件 then 值 else 值 end 字段)。但是这⾥直接就case 然后中间⼀样之后就end了。这⼀点是⾮常需要注意的，当然在存储过程中⼜不⼀样，存储过程中的case是以end case结尾的。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论