MySql 排名用户

Question

我正在使用此代码对用户进行排名:

    SELECT @rn:=@rn+1 AS rank, userid, amount
FROM (
  SELECT userid, sum(amount) AS amount
  FROM leads WHERE date(time)='2013-09-15'
  GROUP BY userid
  ORDER BY amount DESC
) t1 , (SELECT @rn:=0) t2;

结果是这样的:

rank    userid  amount
1         11    1.15
2         10    1.15

即使用户拥有相同的数量，它也会不断增加排名，有什么解决办法吗？是的，我在 stackoverflow 上搜索了 google 和此处，但我无法解决这个问题。

Answer 1

首先，你不需要子查询来做你想做的事。

下面通过引入另一个变量来记住总量，对金额进行密集排序:

SELECT userid, sum(amount) AS amount,
       if(@amount = amount, @rn, @rn := @rn + 1) as ranking,
       @amount := amount
FROM leads cross join
     (select @rn := 0, @amount := -1) const
WHERE date(time) = '2013-09-15'
GROUP BY userid
ORDER BY amount DESC;