如何在 MySQL 中对具有相同权重的记录进行排名 [英] How to rank a record with same weight in MySQL

问题描述

假设我有一个数据说:

  Name   | Marks   
StudentA | 90
StudentB | 85
StudentC | 85
StudentD | 70

现在学生A将获得第一名，学生B和学生C将获得第二名，学生D将获得第四名.

now StudentA will get 1st Rank, StudentB and StudentC will get 2nd Rank and Student D will get 4th Rank.

如果没有重复的权重，我知道基本的排名计算，但是如果我们遇到 2 个相似的权重，如何处理，因为在这种情况下有两个 85 标记将共享排名 2.

I know the basic rank computation if there are no duplicate weights, but how to handle if we encounter 2 similar weights as in this case there are two 85 marks which will share rank 2.

推荐答案

使用 Giorgos 的小提琴...

Using Giorgos's fiddle...

SELECT name
     , marks
     , FIND_IN_SET(marks, (SELECT GROUP_CONCAT(marks ORDER BY marks DESC) FROM mytable)) rank
  FROM mytable;

|     Name | Marks | rank |
|----------|-------|------|
| StudentA |    90 |    1 |
| StudentB |    85 |    2 |
| StudentC |    85 |    2 |
| StudentD |    70 |    4 |

http://sqlfiddle.com/#!9/7cc30/6

或

SELECT name, marks, rank
FROM (SELECT name
     , marks
     , @prev := @curr
     , @curr := marks
     , @i:=@i+1 temp
     , @rank := IF(@prev = @curr, @rank, @i) AS rank
  FROM mytable
     , ( SELECT @curr := null, @prev := null, @rank := 0, @i:=0) vars
 ORDER 
    BY marks DESC,name
      ) x
      ORDER 
    BY marks DESC,name

http://sqlfiddle.com/#!9/287e07/9

这篇关于如何在 MySQL 中对具有相同权重的记录进行排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！