Mysql 查询实现成绩排名

Mysql 查询实现成绩排名，相同分数名次相同，类似于rank()函数

近日系统要实现总分成绩排名，而且相同分数的学生排名要一样，在网上搜了一圈，没有找到合适的方法，只能靠自己实现了，这里提供两种方法

//还有其他排名方式可以借鉴https://blog.youkuaiyun.com/a9925/article/details/76804951

1、sql查询实现

测试如下：

mysql>select*from score ;
+----------+--------------+---------------------+--------------+-------+| study_no | student_name | subject_id          | subject_name | score |+----------+--------------+---------------------+--------------+-------+| student1 | student1     | CodeCourseSubject_0 | 语文         |120|| student2 | student2     | CodeCourseSubject_0 | 语文         |110|| student3 | student3     | CodeCourseSubject_0 | 语文         |110|| student4 | student4     | CodeCourseSubject_0 | 语文         |80|| student5 | student5     | CodeCourseSubject_0 | 语文         |81|| student1 | student1     | CodeCourseSubject_2 | 英语         |150|| student2 | student2     | CodeCourseSubject_2 | 英语         |130|| student3 | student3     | CodeCourseSubject_2 | 英语         |130|| student4 | student4     | CodeCourseSubject_2 | 英语         |44|| student5 | student5     | CodeCourseSubject_2 | 英语         |45|+----------+--------------+---------------------+--------------+-------+10rowsinset

首先这里对科目显示进行了行列转换，并且对以学号study_no进行分组计算总分，并且按排名排序，

sql如下：

1 2 3 4 5 6 7 8 9 10 11

SELECT @rownum:=@rownum+1 AS rownum, 　　　if(@total=total,@rank,@rank:=@rownum)as rank, 　　　@total:=total, 　　　A.* 　　　FROM　(SELECT study_no AS studyNo, 　　　　　　　 student_name AS studentName, 　　　　　　　 SUM(score) AS total, 　　　　　　　 SUM(IF(subject_id='CodeCourseSubject_0',score,0)) AS 语文, 　　　　　　　 SUM(IF(subject_id='CodeCourseSubject_2',score,0)) AS 英语 　　　　　　　 FROM score GROUP BY study_no ORDER BY total DESC 　　　　　　　)A,(SELECT @rank:=0,@rownum:=0,@total:=null)B

　　

结果：

+--------+------+---------------+----------+-------------+-------+-------+-------+| rownum | rank |@total:=total | studyNo  | studentName | total | 语文  | 英语  |+--------+------+---------------+----------+-------------+-------+-------+-------+|1|1|270.0| student1 | student1    |270.0|120.0|150.0||2|2|240.0| student2 | student2    |240.0|110.0|130.0||3|2|240.0| student3 | student3    |240.0|110.0|130.0||4|4|126.0| student5 | student5    |126.0|81.0|45.0||5|5|124.0| student4 | student4    |124.0|80.0|44.0|+--------+------+---------------+----------+-------------+-------+-------+-------+5rowsinset

可见排名第二名有两个相同的分数，后面的排名自动往后移。

下面提供另外一种在程序中实现的方法：

2、程序实现

首先获得全班总分并且以降序排序

1 2 3 4 5

public List<Object[]> findAllTotalScore() { String sql = "SELECT study_no,sum(score) AS total from score s "; javax.persistence.Query query = em.createNativeQuery(sql); return query.getResultList(); }

对获得的总分信息进行排序和封装成map(study_no，rank)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

private Map<String, Long> rank(List<Object[]> objects) { long previousRank = 1; double total = 0; //记录排名的map，map<study_no，排名> Map<String, Long> rankMap = new HashMap<>(); for (int i = 0; i < objects.size(); i++) { Object[] object = objects.get(i); //计算名次，相同分数排名一样 if (total == (double) object[1]) { rankMap.put(object[0].toString(), previousRank); } else { rankMap.put(object[0].toString(), i + 1L); total = (double) object[1]; previousRank = i + 1; } } return rankMap; }

使用map直接根据学号就可以获得成绩排名。