智能用户排名计算

Question

我有一个纸牌游戏，其中用户是根据乳清赢得了多少游戏排名。总体评分是预先计算的，以便能够快速加载，但我有关于排名计算的问题。

排名的计算方法如下：

SET @userRank = 0;

UPDATE t_ratings AS r
JOIN
  (
    SELECT
        userId, (@userRank := @userRank + 1) as rank
        FROM (
        SELECT
            r.userId,
            r.solvedCount,
            r.playedCount
       FROM
        t_ratings AS r                        
    ORDER BY r.solvedCount DESC , r.playedCount ASC) AS t
  ) AS rt
  ON rt.userId = r.userId
SET r.rank = rt.rank

但最近，我有时开始收到以下错误：

Deadlock found when trying to get lock; try restarting transaction

因此，我想知道是否有更好的方法来计算用户等级以避免死锁？

Answer 1

这就是我在SQL Server中的做法...因为我不是mySQL的人，所以我不确定mySQL是否有窗口函数。

With    cte As
(
        Select  Rank() Over (ORDER BY r.solvedCount DESC , r.playedCount ASC) As [rank], 
                userID
        From    t_ratings As r
)
Update  tr
Set     [rank] = c.[rank]
From    t_ratings tr
Join    cte c
        On  tr.userID = c.userID