mysql - 智能用户等级计算

我有一个纸牌游戏，用户是按赢了多少游戏来排名的。总评分是预先计算，以便能够快速加载，但我有关于排名计算的问题。
等级计算如下：

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

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

最佳答案

这就是我在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

关于mysql - 智能用户等级计算，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13928349/