一种选择是使用排名变量，例如：
UPDATe playerJOIN (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 playerJOIN (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 playerJOIN (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 playerJOIN (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)
注意：请考虑，我建议的查询可以进一步简化。
