我的问题是:

SET @rank=0;

SELECT @rank := @rank +1 AS rank_id, name, SUM(points) AS points
  FROM battle_points
  WHERE category =  'test'
  AND user_id !=0
  GROUP BY user_id
  ORDER BY points DESC;

我想在总分的基础上加一列。使用此查询,这些点很好,但是rank虚拟列不匹配。
例如,点数最多的顶级用户具有rank_idrank,而26列的值为rank_id
如何将24列与points列匹配?
注意:虽然我完全精通PHP,但我只需要一个针对MySQL的解决方案。

最佳答案

您的路径是正确的,但您需要将主查询放入子查询中,以便在进行排名计算之前进行排序,如下所示:

SET @rank=0;
SELECT @rank := @rank +1 AS rank_id, mainQ.*
FROM (
   SELECT name, SUM(points) AS points
   FROM battle_points
   WHERE category =  'test'
      AND user_id !=0
   GROUP BY user_id
   ORDER BY points DESC
) AS mainQ
;

编辑:限定*mainQ.*

07-24 09:44
查看更多