有关系时的MySQL排名 [英] MySQL Rank in the Case of Ties

问题描述

在MySQL中排名时，我需要一些有关关系的帮助.例如:

I need some help dealing with ties when ranking in MySQL. For example:

PLAYER |要点

PLAYER | POINTS

• 玛丽:90
• 鲍勃:90
• Jim:65
• 凯文:12

鲍勃和玛丽都应该排名第一.吉姆应该是第三名.凯文应该是#4.

Bob and Mary should both be ranked #1. Jim should be #3. Kevin should be #4.

MySQL:

SET @rank=0;
SELECT @rank:=@rank +1 as rank, player, points FROM my_table

如何更改SELECT语句，以便在平局时排名正确?

How can I change the SELECT statement so that the ranking is correct in the case of ties?

我的现实生活中的问题更加复杂，但是如果我了解如何解决上述问题，那我应该定下来.

My real life problem is more complicated, but if I understand how to solve the above, then I should be set.

推荐答案

假设名称是唯一的

SELECT t1.name, (SELECT COUNT(*) FROM table_1 t2 WHERE t2.score > t1.score) +1
AS rnk
FROM table_1 t1

这篇关于有关系时的MySQL排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！