Обновление MySQL для сохранения позиций рейтинга

Question

Я пытаюсь разобраться в вопросе и просто не могу понять. Буду признателен, если кто-нибудь даст мне указатель. В качестве простого примера того, чего я пытаюсь достичь, у меня есть эти записи в базе данных.

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

И я хотел бы, чтобы поле "Рейтинг" содержало 1,2,3, основываясь на том, кто набрал наибольшее количество очков, поэтому результат должен быть:

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

В настоящий момент я делаю следующий цикл для всех этих записей, но, учитывая, что на самом деле это может быть несколько тысяч - это может занять вечность! У кого-нибудь есть идея по волшебному запросу, который сделает это за один раз?

Answer 1

В MySQL вы можете использовать row_number.

Вот пример использования его в SELECT:

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

если ты INSERT INTO используя SELECT как это, вы получите свой рейтинг.

Answer 2

Вот способ сделать это:

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 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]

Выбрать:

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`

я не делал никаких проверок производительности на этом, кроме тестирования, что он работает