SQL Server dense_rank 与总和

Question

我有一个查询，它没有返回正确的结果，我希望我的查询返回相同 user_id 的总分，这样每个 user_id 将只有一个记录及其所有分数的总和。

我的查询是这样的:

SELECT
    DENSE_RANK() OVER (ORDER BY score DESC) AS rank,
    user_id, 
    SUM(score) AS total_score
FROM 
    account_game
GROUP BY
    user_id, score
ORDER BY
    rank ASC

查询输出是:

   rank user_id total_score
   1      2          4837
   2      1          600
   2      6          600
   3      1          30
   4      1          20

user_id为1,2,6的应该有3条记录

预期结果应该是

  rank  user_id total_score
  -------------------------
   1     2      4837
   2     6      700
   3     1      650

请推荐

Answer 1

正如 StuartLC 评论的那样，您只需从 GROUP BY 中删除 score 就可以了:

SELECT DENSE_RANK() OVER (Order by SUM(score) DESC) AS rank,
user_id, 
SUM(score) as total_score
FROM 
account_game
GROUP BY user_id
ORDER BY rank ASC