MySQL成绩排行，最高、最低、平均分，正确率

注：本文结果均为纯SQL实现，不喜勿喷，欢迎指教

表结构与测试数据如下所示

单个学生对应单场考试且只有一次考试成绩时：
单个学生对应单场考试有多次考试成绩时：

单个学生对应单场考试且只有一次考试成绩时

计算成绩与排行

顺序排行

SQL语句：

SELECT * FROM (SELECT id,exam_id,member_id,score,(@rank := @rank+1) AS rank FROM `test_score` AS s,(SELECT @rank := 0) AS r WHERE exam_id=1 ORDER BY score DESC) AS c

@rank := 0 代表设置一个名为rank的变量且初始值为0

结果：

并列排行

SELECT c.id,c.exam_id,c.member_id,c.score,c.rank FROM (SELECT id,exam_id,member_id,score,IF(score=@last_score,@rank:=@rank,@rank:=@rank + 1) AS rank,@last_score:=score FROM `test_score` AS s,(SELECT @rank := 0,@last_score:=0) AS r WHERE exam_id = 1 ORDER BY score DESC) AS c

@last_score: 用于记录上一行的分数

结果：

并列排行空缺

SELECT c.id,c.exam_id,c.member_id,c.score,c.rank FROM (SELECT id,exam_id,member_id,score,@group_count:=@group_count+1 AS group_count,IF(score=@last_score,@rank:=@rank,@rank:=@group_count) AS rank,@last_score:=score FROM `test_score` AS s,(SELECT @rank := 0,@last_score:=0,@group_count:=0) AS r WHERE exam_id = 1 ORDER BY score DESC) AS c

@group_count: 用于记录每一行的序号

结果：

计算最高、最低、平均分

SQL语句：

SELECT MAX(score) AS max_score, MIN(score) AS min_score, ROUND(AVG(score), 2) AS avg_score FROM `test_score` WHERE exam_id=1

ROUND(AVG(score), 2) 表示求平均值之后，将结果保留两位小数

结果：

求正确率

公式：(正确数量/总数)*100
由于当前所设计的表中存放的为id字符串，所以还需要计算数量，所以就需要用到MySQL中的函数：length(字段名) 计算长度，这样得出的结果为字段中所有字符的长度，并不是我们需要的根据字符分割计数。稍微变通一下，先计算出所有长度，再计算出去除分隔符的长度，相减得出分隔符数量，再加1则为根据分隔符分割的数量：

(LENGTH(correct_ids)-LENGTH(REPLACE(correct_ids,',','')) +1)
eg.(LENGTH('1,2,3')-LENGTH(REPLACE('1,2,3',',','')) +1)  # 结果为3

这是MySQL中的替换函数
REPLACE(字段名, 要替换的字符串, 替换之后的字符串)

但是这样做有一个漏洞，当字段为空的时候，计算出来的结果是1，所以则需要使用MySQL中的函数: case when来进行判断，简单介绍如下：

case when存在两种用法
1.简单函数：类似程序中的switch case
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
eg.
	CASE sex WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '错误' END
2.搜索函数：搜索满足条件的数据并返回
CASE WHEN [expr] THEN [result1]…ELSE [default] END
eg. CASE WHEN sex=1 THEN '这是男的' WHEN sex=2 THEN '这是女的' ELSE '这是错误的' END

完整SQL：

SELECT id,exam_id,member_id,score,correct_ids,error_ids,CASE WHEN (correct_ids <> '' AND correct_ids IS NOT NULL) AND (error_ids <> '' AND error_ids IS NOT NULL) THEN (ROUND((LENGTH(correct_ids)-LENGTH(REPLACE(correct_ids,',','')) +1) / ((LENGTH(correct_ids)-LENGTH(REPLACE(correct_ids,',','')) +1) + (LENGTH(error_ids)-LENGTH(REPLACE(error_ids,',','')) +1)) * 100,2)) WHEN correct_ids <> '' AND error_ids = '' THEN 100.00 ELSE 0.00 END AS '正确率' FROM `test_score` WHERE exam_id = 1

结果：

单个学生对应单场考试存在多次考试成绩时

计算成绩与排行与正确率

计算学生参加考试次数、最高成绩、平均成绩、总正确率及排行。学生存在多次考试成绩时以最高成绩为准进行排行
SQL语句：

SELECT c.* FROM (SELECT exam_id,member_id,nums,score,avg_score,correct_rate AS '正确率',(@rank := @rank + 1) AS rank FROM (SELECT exam_id,member_id,COUNT(*) AS nums,MAX(score) AS score,GROUP_CONCAT(correct_ids) AS correct_ids,GROUP_CONCAT(error_ids) AS error_ids,ROUND(AVG(score), 2) AS avg_score,CASE WHEN (GROUP_CONCAT(correct_ids) <> '' AND GROUP_CONCAT(correct_ids) IS NOT NULL) AND (GROUP_CONCAT(error_ids) <> '' AND GROUP_CONCAT(error_ids) IS NOT NULL) THEN (ROUND(CASE WHEN RIGHT(GROUP_CONCAT(correct_ids), 1) = ',' AND LEFT(GROUP_CONCAT(correct_ids), 1) = ',' THEN (LENGTH(GROUP_CONCAT(correct_ids)) - LENGTH(REPLACE(GROUP_CONCAT(correct_ids), ',', ''))) - 1 WHEN RIGHT(GROUP_CONCAT(correct_ids), 1) = ',' OR LEFT(GROUP_CONCAT(correct_ids), 1) = ',' THEN (LENGTH(GROUP_CONCAT(correct_ids)) - LENGTH(REPLACE(GROUP_CONCAT(correct_ids), ',', ''))) ELSE (LENGTH(GROUP_CONCAT(correct_ids)) - LENGTH(REPLACE(GROUP_CONCAT(correct_ids), ',', '')) + 1) END / (CASE WHEN RIGHT(GROUP_CONCAT(correct_ids), 1) = ',' AND LEFT(GROUP_CONCAT(correct_ids), 1) = ',' THEN (LENGTH(GROUP_CONCAT(correct_ids)) - LENGTH(REPLACE(GROUP_CONCAT(correct_ids), ',', ''))) - 1 WHEN RIGHT(GROUP_CONCAT(correct_ids), 1) = ',' OR LEFT(GROUP_CONCAT(correct_ids), 1) = ',' THEN (LENGTH(GROUP_CONCAT(correct_ids)) - LENGTH(REPLACE(GROUP_CONCAT(correct_ids), ',', ''))) ELSE (LENGTH(GROUP_CONCAT(correct_ids)) - LENGTH(REPLACE(GROUP_CONCAT(correct_ids), ',', '')) + 1) END + CASE WHEN RIGHT(GROUP_CONCAT(error_ids), 1) = ',' AND LEFT(GROUP_CONCAT(error_ids), 1) = ',' THEN (LENGTH(GROUP_CONCAT(error_ids)) - LENGTH(REPLACE(GROUP_CONCAT(error_ids), ',', ''))) - 1 WHEN RIGHT(GROUP_CONCAT(error_ids), 1) = ',' OR LEFT(GROUP_CONCAT(error_ids), 1) = ',' THEN (LENGTH(GROUP_CONCAT(error_ids)) - LENGTH(REPLACE(GROUP_CONCAT(error_ids), ',', ''))) ELSE (LENGTH(GROUP_CONCAT(error_ids)) - LENGTH(REPLACE(GROUP_CONCAT(error_ids), ',', '')) + 1) END) * 100,2)) WHEN GROUP_CONCAT(correct_ids) <> '' AND GROUP_CONCAT(error_ids) = '' THEN 100.00 ELSE 0 END AS correct_rate FROM `test_score` WHERE exam_id = 1 GROUP BY member_id) AS em,(SELECT (@rank := 0)) AS r ORDER BY em.score DESC) AS c ORDER BY rank ASC

结果：

字符串截取函数：
left(str, length)：length必须大于0，从str左边开始截取
eg.left('test', 2)  # 结果：te
right(str, length)：length必须大于0，从str右边开始截取
eg.right('test', 2)  # 结果：st