MySQL更新语句以存储排名位置

Question

我试图理解一个查询但是无法弄清楚。如果有人能够给予我一些指引，我将不胜感激。作为我试图实现的简单示例，我在数据库中有以下记录

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

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

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

目前，我正在为所有这些记录执行一个for next循环，但实际上可能有几千个 - 那可能需要很长时间！是否有任何魔法查询的想法，可以一次完成所有任务？

- user385762

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;

- quantumSoup

---

看看DOK的版本 - 它不需要SET语句，它完全是自包含的。 - OMG Ponies

可以的，这句话的意思是：从单个查询结果来看，是自给自足的；但这是因为他在SELECT子查询中设置了@rownum变量。我认为这会显得比较混乱。 - quantumSoup

美在观者眼中，但能够提交单个语句的能力更不容易出现问题。 - OMG Ponies

我认为应该按升序排序？ - Funzo

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。

- DOK

---

+1：通常我会说这应该在视图中完成，但是MySQL视图不允许使用变量。有一种替代方法是在子查询中使用COUNT来获取排名值，这应该可以在视图中使用... - OMG Ponies

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 表的主键。

- Jason McCreary

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

- Ragowit

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;

- Colen

Answer 6

我展示给你我的做法[用于间隔SQL更新函数]。

选择：

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

更新：

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`

我没有对这个进行任何性能检查，只是测试了它是否工作正常。

- vortex