Обновите рейтинг в таблице MySQL
У меня есть следующая структура таблицы для таблицы Player
Table Player {
Long playerID;
Long points;
Long rank;
}
Предполагая, что playerID и очки имеют действительные значения, могу ли я обновить рейтинг для всех игроков на основе количества очков в одном запросе? Если два человека имеют одинаковое количество очков, они должны связать для ранга.
ОБНОВИТЬ:
Я использую hibernate, используя запрос, предложенный как собственный запрос. Hibernate не любит использовать переменные, особенно ':'. Кто-нибудь знает какие-либо обходные пути? Или не используя переменные или обходя ограничения hibernate в этом случае с помощью HQL?
4 ответа
Один из вариантов - использовать переменную ранжирования, например:
UPDATE player
JOIN (SELECT p.playerID,
@curRank := @curRank + 1 AS rank
FROM player p
JOIN (SELECT @curRank := 0) r
ORDER BY p.points DESC
) ranks ON (ranks.playerID = player.playerID)
SET player.rank = ranks.rank;
JOIN (SELECT @curRank := 0) часть позволяет инициализировать переменную, не требуя отдельного SET команда.
Дальнейшее чтение по этой теме:
- SQL: ранжирование без самостоятельного объединения
- Переполнение стека: создание столбца накопительной суммы в MySQL
Прецедент:
CREATE TABLE player (
playerID int,
points int,
rank int
);
INSERT INTO player VALUES (1, 150, NULL);
INSERT INTO player VALUES (2, 100, NULL);
INSERT INTO player VALUES (3, 250, NULL);
INSERT INTO player VALUES (4, 200, NULL);
INSERT INTO player VALUES (5, 175, NULL);
UPDATE player
JOIN (SELECT p.playerID,
@curRank := @curRank + 1 AS rank
FROM player p
JOIN (SELECT @curRank := 0) r
ORDER BY p.points DESC
) ranks ON (ranks.playerID = player.playerID)
SET player.rank = ranks.rank;
Результат:
SELECT * FROM player ORDER BY rank;
+----------+--------+------+
| playerID | points | rank |
+----------+--------+------+
| 3 | 250 | 1 |
| 4 | 200 | 2 |
| 5 | 175 | 3 |
| 1 | 150 | 4 |
| 2 | 100 | 5 |
+----------+--------+------+
5 rows in set (0.00 sec)
ОБНОВЛЕНИЕ: Просто заметил, что вам нужны связи, чтобы иметь одинаковый ранг. Это немного сложно, но может быть решено с еще большим количеством переменных:
UPDATE player
JOIN (SELECT p.playerID,
IF(@lastPoint <> p.points,
@curRank := @curRank + 1,
@curRank) AS rank,
@lastPoint := p.points
FROM player p
JOIN (SELECT @curRank := 0, @lastPoint := 0) r
ORDER BY p.points DESC
) ranks ON (ranks.playerID = player.playerID)
SET player.rank = ranks.rank;
Для теста давайте добавим еще одного игрока с 175 очками:
INSERT INTO player VALUES (6, 175, NULL);
Результат:
SELECT * FROM player ORDER BY rank;
+----------+--------+------+
| playerID | points | rank |
+----------+--------+------+
| 3 | 250 | 1 |
| 4 | 200 | 2 |
| 5 | 175 | 3 |
| 6 | 175 | 3 |
| 1 | 150 | 4 |
| 2 | 100 | 5 |
+----------+--------+------+
6 rows in set (0.00 sec)
И если вам требуется звание, чтобы пропустить место в случае ничьей, вы можете добавить еще один IF состояние:
UPDATE player
JOIN (SELECT p.playerID,
IF(@lastPoint <> p.points,
@curRank := @curRank + 1,
@curRank) AS rank,
IF(@lastPoint = p.points,
@curRank := @curRank + 1,
@curRank),
@lastPoint := p.points
FROM player p
JOIN (SELECT @curRank := 0, @lastPoint := 0) r
ORDER BY p.points DESC
) ranks ON (ranks.playerID = player.playerID)
SET player.rank = ranks.rank;
Результат:
SELECT * FROM player ORDER BY rank;
+----------+--------+------+
| playerID | points | rank |
+----------+--------+------+
| 3 | 250 | 1 |
| 4 | 200 | 2 |
| 5 | 175 | 3 |
| 6 | 175 | 3 |
| 1 | 150 | 5 |
| 2 | 100 | 6 |
+----------+--------+------+
6 rows in set (0.00 sec)
Примечание: пожалуйста, учтите, что предлагаемые мной запросы могут быть еще более упрощены.
Даниэль, у тебя очень хорошее решение. За исключением одного пункта - чехол для галстука. Если между 3 игроками происходит ничья, это обновление не работает должным образом. Я изменил ваше решение следующим образом:
UPDATE player
JOIN (SELECT p.playerID,
IF(@lastPoint <> p.points,
@curRank := @curRank + @nextrank,
@curRank) AS rank,
IF(@lastPoint = p.points,
@nextrank := @nextrank + 1,
@nextrank := 1),
@lastPoint := p.points
FROM player p
JOIN (SELECT @curRank := 0, @lastPoint := 0, @nextrank := 1) r
ORDER BY p.points DESC
) ranks ON (ranks.playerID = player.playerID)
SET player.rank = ranks.rank;
РЕДАКТИРОВАТЬ: Заявление об обновлении, представленное ранее, не работает.
Хотя это не совсем то, что вы просите: вы можете генерировать рейтинг на лету, выбирая:
select p1.playerID, p1.points, (1 + (
select count(playerID)
from Player p2
where p2.points > p1.points
)) as rank
from Player p1
order by points desc
РЕДАКТИРОВАТЬ: попробуйте оператор UPDATE еще раз. Как насчет временной таблицы:
create temporary table PlayerRank
as select p1.playerID, (1 + (select count(playerID)
from Player p2
where p2.points > p1.points
)) as rank
from Player p1;
update Player p set rank = (select rank from PlayerRank r
where r.playerID = p.playerID);
drop table PlayerRank;
Надеюсь это поможет.
Согласно правилам нормализации, ранг должен оцениваться во время SELECT.