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

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

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 |+----------+--------------+---------------------+--------------+-------+10 rows in set
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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

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
1
2
3
4
5
6
7
8
9
10
11
1
2
3
4
5
6
7
8
9
10
11

结果：

+--------+------+---------------+----------+-------------+-------+-------+-------+| 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 |+--------+------+---------------+----------+-------------+-------+-------+-------+5 rows in set
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10

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

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

2、程序实现

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

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();    }
1
2
3
4
5
1
2
3
4
5

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

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;    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

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