mysql - MySql 获取玩家最近几场比赛的总分

Question

我的名为 match_info 的 MySql 表大致如下所示 -

    match_no | player_id | score

       1     |  1        |  50
       1     |  2        |  12
       2     |  1        |  10
       2     |  2        |  14
       3     |  1        |  11
       3     |  2        |  30

实际表格包含的数据比这多得多。不管怎样，为了举例，让我们假设这是现在的表格。我想要的是找到每个球员在最近比赛中选定数量的总得分。例如说现在我只需要计算最近的 2 场比赛。所以只匹配没有。 3 和 2 应该算在内，我的输出应该是 -

player_id |  score
  2       |   44
  1       |   21

我该怎么做？我尝试了以下 -

SELECT player_id,SUM(score) as total_score 
FROM  match_info 
where match_no IN
(select match_no 
from match_info 
ORDER BY match_no DESC LIMIT 2)
group by player_id 
order by total_score desc;

然而我遇到的错误是-

This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

所以我不能在“IN”子查询中使用限制。我可以使用什么替代方法？

Answer 1

尝试使用此查询，它按预期工作。

SELECT player_id, SUM(score) FROM match_info m1 INNER JOIN (SELECT DISTINCT match_no FROM match_info ORDER BY match_no DESC LIMIT 2) m2 ON m1.`match_no` = m2.match_no GROUP BY `player_id` ORDER BY `player_id`