MySQL更新语句存储排名位置

Question

我正在尝试解决一个问题，但我就是想不通。如果有人给我指点，我将不胜感激。作为我想要实现的目标的一个简单示例，我在数据库中有这些记录，

Score|Ranking
-------------
100  |0
200  |0
300  |0

并且我希望排名字段根据得分最高的人包含 1,2,3，因此结果应该是：

Score|Ranking
-------------
100  |3
200  |2
300  |1

目前，我正在为所有这些记录执行 for next 循环，但考虑到实际上可能有几千个 - 这可能需要永远！有谁知道可以一次性完成此操作的神奇查询吗？

Answer 1

这是一种方法：

SET @r=0;
UPDATE table SET Ranking= @r:= (@r+1) ORDER BY Score DESC;

/* use this if you just want to pull it from the db, but don't update anything */
SET @r=0;
SELECT *, @r:= (@r+1) as Ranking FROM table ORDER BY Score DESC;

Answer 2

在 MySQL 中，您可以使用 row_number。

这是一个使用它的示例在 SELECT 中：

select @rownum:=@rownum+1 ‘rank’, p.* 
from player p, (SELECT @rownum:=0) r 
order by score desc;

如果您使用这样的 SELECT INSERT INTO，您将获得排名。

Answer 3

这将创建一个内联更新语句，该语句将按变量 @rc 递增对玩家进行排名。我在非常类似的情况下使用过它很多次，它运行良好并将其全部保留在数据库端。

SET @rc = 0;
UPDATE players JOIN (SELECT @rc := @rc + 1 AS rank, id FROM players ORDER BY rank DESC)
AS order USING(id) SET players.rank = order.rank;

假定 id 是您的 players 表的主键。

Answer 4

如果您使用的是 MySQL 8，那么您可以使用新函数 RANK()

SELECT
    score,
    RANK() OVER (
       ORDER BY score DESC
    ) ranking
FROM
    table;

根据您想要如何显示平均分数的排名，您也可以查看 DENSE_RANK()

作为更新：

WITH
   ranking AS(
   SELECT
      score,
      RANK() OVER (
         ORDER BY score DESC
      ) ranking
    FROM
        table
)
UPDATE
    table,
    ranking r
SET
    table.ranking = r.ranking
WHERE
    table.score = r.score

Answer 5

SET @r = 0;
UPDATE players JOIN (SELECT @r := @r + 1 AS rank, id FROM players ORDER BY rank DESC)
AS sorted USING(id) SET players.rank = sorted.rank;

Answer 6

我正在向您展示我的做法[对于间隔sql更新功能]

select:

set @currentRank = 0,
    @lastRating = null,
    @rowNumber = 1;
select
    *,
    @currentRank := if(@lastRating = `score`, @currentRank, @rowNumber) `rank`,
    @rowNumber := @rowNumber + if(@lastRating = `score`, 0, 1) `rowNumber`,
    @lastRating := `score`
from `table`
order by `score` desc

update:

set @currentRank = 0,
    @lastRating = null,
    @rowNumber = 1;
update 
    `table` r
    inner join (
        select
            `primaryID`,
            @currentRank := if(@lastRating = `score`, @currentRank, @rowNumber) `rank`,
            @rowNumber := @rowNumber + if(@lastRating = `score`, 0, 1) `rowNumber`,
            @lastRating := `score`
        from `table`
        order by `score` desc
    ) var on
        var.`primaryID` = r.`primaryID`
set
    r.`rank` = var.`rank`

除了测试它是否有效之外，我没有对此进行任何性能检查