mysql - 选择每个测验的最佳分数和积分排行榜

Question

我的 MySql 表看起来像这样

quiz_id | user_id | points 
--------+---------+--------
1       | 1       | 2         
1       | 1       | 7         
3       | 1       | 7       
2       | 1       | 5      
1       | 2       | 7      
2       | 2       | 7      
3       | 2       | 2 
3       | 2       | 6

用户可以重玩游戏以获得最高分..

我需要查询以获取用户玩的每个测验的最佳“最大”分数，并按所有测验的总分对其进行排序，并使其看起来像这样:

   user_id  | SUM(points) 
    --------+--------
    2       | 20             
    1       | 19      

我试着用这个做第二部分

SELECT user_id, SUM(points) 
FROM Quiz_list 
GROUP BY user_id 
ORDER BY SUM(points) DESC   

如果有人能告诉我它应该如何作为 sql 查询，我将不胜感激。

Answer 1

我认为您需要一个子查询来获取 max() 然后执行 sum():

select user_id, sum(points) as sumpoints
from (select user_id, quiz_id, max(points) as points
      from quiz_list
      group by user_id, quiz_id
     ) uq
group by user_id
order by sumpoints desc;

SQL fiddle :http://sqlfiddle.com/#!2/de1136/1/0