mysql 更新 排名_更新MySQL表中的排名

一种选择是使用排名变量,例如：

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命令.

进一步阅读这个主题：

测试用例：

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)

注意：请考虑我建议的查询可以进一步简化.