sql - 找到正确的位置插入新的高分记录

Question

给定表格:

ArcadeScores
------------
ID
GameID
UserID
Score
Milliseconds
Rank

其中 Rank > 0 并根据 Score DESC 的索引然后按毫秒 ASC 计算(最好的分数总是最高的，如果分数相等，则由谁排名)做得最快)。

存储 Rank 是必需的，因为它允许我执行快速查询，例如 How many top 3 scores does userID 5 have?。

通过对所有记录进行排序并循环更新排名，在插入新分数时重新计算 GameID 的 Rank 可以正常工作，但循环遍历每条记录并当您有数千条记录时，对每条记录执行更新查询会减慢速度。对于流行的游戏(尤其是单个用户可能每 3 秒左右发布一个新分数的快速游戏)，这成本太高了。

给定一个新的分数记录，我需要计算出它应该被插入到哪个位置。如果我们的新记录将排在 45 之后，我们可以将其上方的每条记录递增一个，这是一种成本更低的操作:

UPDATE ArcadeScores SET ScoreRank = ScoreRank + 1 WHERE gameID = " + myGameID + " AND ScoreRank >= 45

我遇到的困难是确定要插入的记录的记录等级。仅在 Score 或 Milliseconds 上它相当容易，但我正在努力让它发现正确的 Rank 作为两者的组合。

一场比赛有多少得分记录是已知值。

Answer 1

您需要查询还是可以使用函数？试试这个查询——如果我正确理解了你表中的顺序，它会给出新行的排名，其值为 inserting_score 和 inserting_milliseconds:

SELECT COUNT(1) + 1 FROM ArcadeScores 
WHERE Score > inserting_score OR (Score = inserting_score AND Milliseconds < inserting_millisecondes)

---

哦，忘了 GameID :)

SELECT COUNT(1) + 1 FROM ArcadeScores 
WHERE GameID = inserting_gameid AND (Score > inserting_score 
OR (Score = inserting_score AND Milliseconds < inserting_millisecondes))