MySQL排名与关系 [英] MySQL Rank with ties
问题描述
我是新的sql,我从来没有使用变量或条件在mysql,但知道从其他编程语言。自从几天后,我试图找到一种方式来排名用户分数。我读了很多文章,还有问题,stackoverflow,最后我发现一个解决方案,几乎是它喜欢我想要的。
I am new to sql and I have never used variables or conditions in mysql, but know that from other programming languages. Since a few days I try to find a way to rank a user score. I read a lot of articles, and also questions that asked on stackoverflow and finally I found a solution that nearly does it like I want it.
SELECT
score_users.uid,
score_users.score,
@prev := @curr,
@curr := score,
@rank := IF(@prev = @curr, @rank, @rank +1) AS rank
FROM
score_users,
(SELECT @curr := null, @prev := null, @rank := 0) tmp_tbl
WHERE
score_users.matchday = 1
ORDER BY
score_users.score DESC
但我的问题是打分。我不想得到连续的等级,例如:
But my Problems are tie scores. I don't want to get consecutive ranks, like this:
+------------+------+--------+
| uid | name | rank | score |
+------------+------+--------+
| 4 | Jon | 1 | 20 |
| 1 | Jane | 2 | 19 |
| 2 | Fred | 2 | 19 |
| 9 | July | 3 | 18 |
| 7 | Mary | 4 | 17 |
| 3 | Toni | 5 | 12 |
| 5 | Steve | 5 | 12 |
| 6 | Peter | 6 | 11 |
| 8 | Nina | 7 | 10 |
+------------+------+--------+
我想得到这样的结果:
+------------+------+--------+
| uid | name | rank | score |
+------------+------+--------+
| 4 | Jon | 1 | 20 |
| 1 | Jane | 2 | 19 |
| 2 | Fred | 2 | 19 |
| 9 | July | 4 | 18 |
| 7 | Mary | 5 | 17 |
| 3 | Toni | 6 | 12 |
| 5 | Steve | 6 | 12 |
| 6 | Peter | 8 | 11 |
| 8 | Nina | 9 | 10 |
+------------+------+--------+
我想我必须创建一个新的临时表,一些如果条件,但我找不到一个解决方案,变得绝望!
此外,我必须关注表现,也许有更好的方式来获得排名在得分,因为我做到了吗?我将非常感谢提示或一些代码片段。
I guess I have to create a new temporary table, and some if conditions, but I couldn't find a solution and become desperate! Also, I have to keep an eye on performance, maybe there are better ways to get the rank on score as I did it? I would be very grateful for hints or some code snippet.
推荐答案
您可以使用另一个变量来计数相同的排名,将 @rank
乘以1,将计数器值增加 @rank
,如下所示:
You can use another variable to count the same ranks so instead of incrementing @rank
by 1, you increment @rank
by the counter value, like this:
SELECT
score_users.uid,
score_users.score,
@prev := @curr,
@curr := score,
@rank := IF(@prev = @curr, @rank, @rank + @i) AS rank,
IF(@prev <> score, @i:=1, @i:=@i+1) AS counter
FROM
score_users,
(SELECT @curr := null, @prev := null, @rank := 0, @i := 0) tmp_tbl
WHERE
score_users.matchday = 1
ORDER BY
score_users.score DESC
这篇关于MySQL排名与关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!