mysql - 带领带的简单 MySQL 更新排名

Question

我正在尝试根据分数存储用户排名，所有这些都放在一张表中，并在出现平局时跳过排名。例如:

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

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

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

但是，就此而言，这不支持平局，或在平局后跳过排名数字。

我想在尽可能少的查询中实现这种重新排名并且没有连接(因为它们看起来相当耗时)。

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

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;

我不想要这些额外的列，但没有它们我无法让单个查询正常工作。

有什么想法吗？

谢谢。

Answer 1

这是一个替代解决方案:根本不存储排名! :-)

您可以即时计算它们。

例子:

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 这是一个辅助列，指示我们是否应该分配下一个等级。

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

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)