有关系的简单MySQL更新等级 [英] Simple MySQL Update Rank with Ties

问题描述

我试图基于分数存储用户排名，所有这些都存储在一张表中，并且在出现平局时跳过排名.例如:

I am attempting to store the rank of users based on a score, all it one table, and skipping ranks when there is a tie. For example:

ID   Score Rank
2    23    1
4    17    2
1    17    2
5    10    4
3    2     5

每次更新用户分数时，还必须更新整个表的排名，因此在分数更新后，将运行以下查询:

Each time a user's score is updated, The rank for the entire table must also be updated, so after a score update, the following query is run:

SET @rank=0;
UPDATE users SET rank= @rank:= (@rank+1) ORDER BY score DESC;

但是，这并不支持平局，或者在平局后不跳过排名数字.

But this doesn't support ties, or skipping rank numbers after ties, for that matter.

我希望在尽可能少的查询中且没有联接的情况下实现这种重新排名(因为它们看起来很耗时).

I want to achieve this re-ranking in as few queries as possible and with no joins (as they seem rather time consuming).

通过使用以下代码添加两列(last_score和tie_build_up)，我能够获得所需的结果:

I was able to get the desired result by adding two columns - last_score and tie_build_up - with the following code:

SET @rank=0, @last_score = null, @tie_build_up = 0;
UPDATE users SET
    rank= @rank:= if(@last_score = score, @rank, @rank+@tie_build_up+1),
    tie_build_up= @tie_build_up:= if(@last_score = score, @tie_build_up+1, 0),
    last_score= @last_score:= score, ORDER BY score DESC;

我不希望这些额外的列，但是没有它们，我无法使单个查询正常工作.

I don't want those extra columns, but I couldn't get the single query to work without them.

有什么想法吗?

谢谢.

推荐答案

这里是另一种解决方案:根本不存储等级！ :-)

Here's an alternate solution: don't store ranks at all! :-)

您可以即时计算它们.

示例:

SELECT id, (@next_rank := IF(@score <> score, 1, 0)) nr, 
           (@score := score) score, (@r := IF(@next_rank = 1, @r + 1, @r)) rank 
FROM rank, (SELECT @r := 0) dummy1
ORDER BY score DESC;

结果:

  +------+----+-------+------+
  | id   | nr | score | rank |
  +------+----+-------+------+
  |    2 |  1 |    23 |    1 |
  |    4 |  1 |    17 |    2 |
  |    1 |  0 |    17 |    2 |
  |    5 |  1 |    10 |    3 |
  |    3 |  1 |     2 |    4 |
  +------+----+-------+------+

nr这是一个辅助列，指示我们是否应该分配下一个等级.

nr here is aт auxilliary column that indicates whether we should assign next rank or not.

例如，您可以将此查询包装在另一个select中并执行分页.

You can wrap this query in another select and perform paging, for example.

SELECT id, score, rank 
FROM (SELECT id, (@next_rank := IF(@score <> score, 1, 0)) nr, 
           (@score := score) score, (@r := IF(@next_rank = 1, @r + 1, @r)) rank
      FROM rank, (SELECT @r := 0) dummy1
      ORDER BY score DESC) t
      WHERE rank > 1 and rank < 3;

结果:

  +------+-------+------+
  | id   | score | rank |
  +------+-------+------+
  |    4 |    17 |    2 |
  |    1 |    17 |    2 |
  +------+-------+------+

注意:由于现在rank是计算所得的列，因此您无法对其进行索引并且无法有效地将其分页到数据集中(即，选择具有3000到3010的等级的记录").但这对于选择前N个排名"还是有好处的(前提是您在查询中放置了相应的LIMIT)

CAUTION: since now rank is a calculated column, you can't index it and efficiently page far into dataset (that is, "select records with ranks from 3000 to 3010"). But it's still good for "select top N ranks" (provided that you put a corresponding LIMIT on a query)

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