更新MySQL表中的等级 [英] Update the rank in a MySQL Table
问题描述
我为表格Player拥有下列表格结构
Table Player {
Long playerID;
长分;
长排名;
$ / code> 假设playerID和points有有效值，我可以更新rank对于基于单个查询中的点数的所有玩家？如果两个人的分数相同，他们应该排在一起。
更新：
我使用hibernate使用建议作为本机查询的查询。 Hibernate不喜欢使用变量，特别是'：'。有谁知道任何解决方法？或者通过不使用变量或者在这种情况下通过使用HQL解决Hibernate的限制问题？
，如下所示： UPDATE player
JOIN（SELECT p.playerID，
@curRank： = @curRank + 1 AS等级
从玩家p
JOIN（SELECT @curRank：= 0）r
ORDER BY p.points DESC
）等级ON（ranks.playerID = player.playerID）
SET player.rank = ranks.rank;
JOIN（SELECT @curRank：= 0）部分允许变量初始化而不需要单独的 SET 命令。
关于此主题的进一步阅读： p>
测试用例：
CREATE TABLE player（
playerID int，
点int，
等级int
）;
插入玩家值（1，150，NULL）;
INSERT INTO玩家VALUES（2，100，NULL）;
INSERT INTO玩家VALUES（3，250，NULL）;
INSERT INTO玩家VALUES（4，200，NULL）;
INSERT INTO玩家VALUES（5，175，NULL）;
UPDATE player
JOIN（SELECT p.playerID，
@curRank：= @curRank + 1 AS等级
从玩家p
JOIN（SELECT @ curRank = 0）r
ORDER BY p.points DESC
）levels ON（ranks.playerID = player.playerID）
SET player.rank = ranks.rank;
结果：
SELECT * FROM player ORDER BY rank;
+ ---------- + -------- + ------ +
| playerID |点|等级|
+ ---------- + -------- + ------ +
| 3 | 250 | 1 |
| 4 | 200 | 2 |
| 5 | 175 | 3 |
| 1 | 150 | 4 |
| 2 | 100 | 5 |
+ ---------- + -------- + ------ +
5行（0.00秒）
更新：刚注意到你需要联系分享相同的等级。这有点棘手，但可以用更多的变量来解决：
UPDATE player
JOIN（SELECT p。 playerID，
IF（@lastPoint<> p.points，
@curRank：= @curRank + 1，
@curRank）AS rank，
@lastPoint：= p .points
从玩家p
JOIN（SELECT @curRank：= 0，@lastPoint：= 0）r
ORDER BY p.points DESC
）levels 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 |点|等级|
+ ---------- + -------- + ------ +
| 3 | 250 | 1 |
| 4 | 200 | 2 |
| 5 | 175 | 3 |
| 6 | 175 | 3 |
| 1 | 150 | 4 |
| 2 | 100 | 5 |
+ ---------- + -------- + ------ +
6行（0.00秒）
如果你需要排名跳过一个地方，你可以添加另一个 IF 条件：
UPDATE player
JOIN（SELECT p.playerID，
IF（@lastPoint<> p.points，
@curRank：= @curRank + 1，
@curRank）AS等级，
IF（@lastPoint = p.points，
@curRank：= @curRank + 1，
@curRank），
@lastPoint：= p.points
从玩家p
JOIN（SELECT @curRank：= 0， @lastPoint：= 0）r
ORDER BY p.points DESC
）levels ON（ranks.playerID = player.playerID）
SET player.rank = ranks.rank;
结果：
SELECT * FROM player ORDER BY rank;
+ ---------- + -------- + ------ +
| playerID |点|等级|
+ ---------- + -------- + ------ +
| 3 | 250 | 1 |
| 4 | 200 | 2 |
| 5 | 175 | 3 |
| 6 | 175 | 3 |
| 1 | 150 | 5 |
| 2 | 100 | 6 |
+ ---------- + -------- + ------ +
6行（0.00秒）
注意：请考虑我建议的查询可以进一步简化。
I have the following table structure for a table Player
Table Player {
Long playerID;
Long points;
Long rank;
}
Assuming that the playerID and the points have valid values, can I update the rank for all the players based on the number of points in a single query? If two people have the same number of points, they should tie for the rank.
UPDATE:
I'm using hibernate using the query suggested as a native query. Hibernate does not like using variables, especially the ':'. Does anyone know of any workarounds? Either by not using variables or working around hibernate's limitation in this case by using HQL?
One option is to use a ranking variable, such as the following:
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;
The JOIN (SELECT @curRank := 0) part allows the variable initialization without requiring a separate SET command.
Further reading on this topic:
Test Case:
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;
Result:
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: Just noticed the that you require ties to share the same rank. This is a bit tricky, but can be solved with even more variables:
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;
For a test case, let's add another player with 175 points:
INSERT INTO player VALUES (6, 175, NULL);
Result:
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)
And if you require the rank to skip a place in case of a tie, you can add another IF condition:
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;
Result:
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)
Note: Please consider that the queries I am suggesting could be simplified further.
这篇关于更新MySQL表中的等级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
