mysql - SQL - 在每个玩家的最后 10 个结果中选择每个玩家的最佳结果

Question

我需要从我的牌 table 创建一个 SQL 请求:

user_id   playDate      score   game_id
1   27/02/2018 16:21    77244   119
1   27/02/2018 16:20    218364  119
1   27/02/2018 16:08    272240  119      <--- This one
1   27/02/2018 16:08    118055  119
1   27/02/2018 16:08    173677  119
1   27/02/2018 16:08    112328  119
1   27/02/2018 16:08    169296  119
1   27/02/2018 16:08    56090   119
1   27/02/2018 16:08    51172   119
1   27/02/2018 16:08    95293   119
1   27/02/2018 16:08    109572  119
1   27/02/2018 16:07    20421   119
1   27/02/2018 16:07    458186  119      <--- NOT This one because not in 10 last play
1   27/02/2018 16:07    196987  119
1   27/02/2018 16:07    75904   119
1   27/02/2018 16:07    117530  119
2   01/03/2018 10:54    223446  119
2   01/03/2018 10:52    617292  119
2   01/03/2018 10:51    177332  119
2   01/03/2018 10:48    41071   119
2   01/03/2018 10:47    103833  119
2   01/03/2018 10:46    327581  119
2   01/03/2018 10:41    737348  119
2   01/03/2018 10:36    8476    119
2   01/03/2018 10:36    252481  119
2   28/02/2018 18:21    205360  119
2   28/02/2018 18:19    63601   119
2   28/02/2018 18:19    242404  119
2   28/02/2018 18:13    18503   119
2   28/02/2018 18:03    167226  119
2   06/01/2018 14:21    386115  119      <--- This one
2   06/01/2018 11:25    216266  119
2   06/01/2018 11:25    149841  119
5   28/02/2018 16:13    402902  119      <--- This one
5   28/02/2018 12:11    156313  119
5   28/02/2018 11:45    192914  119
5   28/02/2018 11:44    117458  119

我的目标是获得:

每个 user_id 的 MAX(score)仅查看 user_id 最近 10 场比赛(通过 Order BY playDate DESC)仅适用于 1 个 game_Id

这些数据的预期结果是:

user_id Best score
  5      402902
  2      386115
  1      272240

我尝试了 GROUP_CONCAT、LIMIT 等，但找不到有效的解决方案。非常感谢 !奥利维亚

Answer 1

我想我找到了解决方案，但任何建议/评论将不胜感激!

SELECT
  play.user_id AS id,
  fos_user.username AS username,
  MAX(play.score) AS score
FROM
  play INNER JOIN (
    SELECT
      user_id,
      GROUP_CONCAT(score ORDER BY playDate DESC) grouped_score
    FROM
      play
    WHERE game_id = :game
    GROUP BY user_id) group_max
  ON play.user_id = group_max.user_id
     AND FIND_IN_SET(score, grouped_score) BETWEEN 1 AND :limit
JOIN fos_user ON play.user_id = fos_user.id 
GROUP BY 
    play.user_id
ORDER BY
    play.score DESC