mysql - 可以将自动递增变量添加到mysql中GROUP BY的行结果中吗？

Question

基本上，我使用此查询根据与用户关联的数字总和对一组用户进行分组。我需要一些如何为每个结果分配索引的信息。我对如何做到这一点感到空白。我想我需要用 AS 来别名，但不确定如何做。有什么想法吗？

这是我动态切换页面的当前查询:

SELECT COUNT(*) as count, user_id, SUM(earnings) as sum FROM ci_league_result
 GROUP BY user_id ORDER BY sum desc LIMIT ".$page.', '.$per;

我正在寻找它的工作方式如下:

SELECT COUNT(*) as count, user_id, SUM(earnings) as sum, *NEW-RESULTS-OVERALL-INDEX* AS newindex FROM ci_league_result
GROUP BY user_id ORDER BY sum desc LIMIT ".$page.', '.$per;

注意第二个查询中的 AS newindex。

感谢您的建议!

Answer 1

试试这个:

SELECT user_id, count, sum, @row := @row + 1 AS newindex FROM
(SELECT
   COUNT(*) as count,
   user_id,
   SUM(earnings) as sum
FROM ci_league_result 
GROUP BY user_id ORDER BY sum desc LIMIT ".$page.', '.$per) r
CROSS JOIN (SELECT @row := 0) rr;

已编辑