mysql对员工进行等级评定_按用户等级对mysql中的用户进行排名

I am trying to rank my students by their points that I've calculated before

but the problem is if students have same points they both should be in same rank

E.g

Student 1 has full points

Student 2 has full points

they both have to be rank as 1;

Here an example of my database

the query I am trying to do is (just for select then I can insert the values to my column)

SELECT a.points

count(b.points)+1 as rank

FROM examresults a left join examresults b on a.points>b.points

group by a.points;

Edit for being more clear:

Student 1 points 80

Student 2 points 77.5

Student 3 points 77.5

Student 4 points 77

their ranks should be like

Student 1 Rank 1

Student 2 Rank 2

Student 3 Rank 2

Student 4 Rank 3

my current query returns a values like

As it is missing the third rank. (because second rank has 2 values)

解决方案

This is just a fix of Gordon solution using variables. The thing is your rank function isnt the way rank should work. (student 4 should be rank 4)

You can add more student to improve the testing.

select er.*,

(@rank := if(@points = points,

@rank,

if(@points := points,

@rank + 1,

@rank + 1

)

)

) as ranking

from students er cross join

(select @rank := 0, @points := -1) params

order by points desc;

OUTPUT

| id | points | ranking |

|----|--------|---------|

| 1 | 80 | 1 |

| 2 | 78 | 2 |

| 3 | 78 | 2 |

| 4 | 77 | 3 |

| 5 | 66 | 4 |

| 6 | 66 | 4 |

| 7 | 66 | 4 |

| 8 | 15 | 5 |