mysql - 通过加入 MYSQL 在查询结果中获得位置

Question

我想在我的查询中获得一个结果的位置，避免出现相同数量的双重结果。结果是用户在我的表中的排名。我的问题是当我有 2 个或更多具有相同分数的用户时。他们将具有相同的等级。这是我现在的查询:

SELECT users.user_name, us.score, COUNT(mrank.id) as rank
  FROM user users
  LEFT OUTER JOIN challenges_score_user_rel us
    ON us.user_id = users.id
  LEFT OUTER JOIN challenges_score_user_rel mrank
    ON mrank.score >= us.score
 GROUP BY users.id
 ORDER BY rank DESC

例如:如果 2 个用户的得分最高并且得分相等，那么他们都将获得第 2 名。

Answer 1

这将通过给具有更大 user_id 的用户更高的排名来打破平局

SELECT users.user_name, us.score,
    (SELECT COUNT(*) FROM challenges_score_user_rel us2
    WHERE (us2.score < us.score OR us2.user_id = us.user_id)
    OR (us2.score = us.score AND us.user_id < us2.user_id)) rank
FROM user users 
LEFT OUTER JOIN challenges_score_user_rel us
    ON us.user_id = users.id
ORDER BY rank DESC

与自连接相同的查询

SELECT users.user_name, us.score, COUNT(*) rank
FROM user users 
LEFT OUTER JOIN challenges_score_user_rel us
    ON us.user_id = users.id
JOIN challenges_score_user_rel us2
    ON (us2.score < us.score OR us2.user_id = us.user_id)
    OR (us2.score = us.score AND us.user_id < us2.user_id)
GROUP BY users.user_name, us.score    
ORDER BY rank  DESC