mysql - wins排名没有平局

Question

我有一个问题，每个用户的排名是完美的，问题是当用户"a“和用户"b”具有相同的结果时。

我的代码

  SELECT ID, Wins, 
    (SELECT COUNT(*)+1 FROM users WHERE Wins>x.Wins) AS rank_upper, 
    (SELECT COUNT(*) FROM users WHERE Wins>=x.Wins) AS rank_lower 
 FROM `users` x WHERE x.username='$_GET[user]'

ID   wins Rank
 1    23    1
 2    17    2
 3    17    2
 4    10    3
 5    10    3

我喜欢这样：

 ID   wins Rank
 1    23    1
 2    17    2
 3    17    3
 4    10    4
 5    10    5

Answer 1

比较id也可能对您有用。

SELECT ID, Wins, 
    (SELECT COUNT(*)+1 FROM users WHERE Wins>x.Wins or (Wins=x.Wins and id<x.id)) AS Rank 
 FROM `users` x WHERE x.username='$_GET[user]'