mysql - 内连接已存在的每组最大 n

Question

我有每组最大 n 问题的简单扩展。

这是我的查询:

$q = 
'SELECT            
    played.words, MAX(played.score) max_score, played.longest, played.game_options, 
    users.first_name 
FROM played 
INNER JOIN users
ON played.user_id = users.user_id       
GROUP BY game_options 
';

不幸的是，虽然我总是获得给定 game_options 的最高分，但 user.first_name 通常是错误的(例如，对于相同的 game_options 值，汤姆先玩并得到 63，而乔第二玩并得到 100；相反Joe 的得分，Tom 返回 Joe 的高分)。

我假设我需要进行某种内部联接，但无法弄清楚，因为我已经有一个关联两个表(用户表和游戏表)的内部联接。我不得不想象这是一个重复的问题，但找不到它。感谢您的帮助。

Answer 1

一种显示所有并列结果的方法:

SELECT            
    p.words, 
    p.score AS max_score, 
    p.longest, 
    p.game_options, 
    u.first_name, 
    m.cnt AS game_played_count
FROM played AS p
  INNER JOIN users AS u
    ON  p.user_id = u.user_id
  INNER JOIN 
    ( SELECT 
          pp.game_options, 
          MAX(pp.score) AS score,
          COUNT(*) AS cnt
      FROM played AS pp      
      GROUP BY pp.game_options 
    ) AS m
    ON  m.game_options = p.game_options
    AND m.score = p.score ;

根据预定义的顺序解决关系:

SELECT            
    p.words, 
    p.score AS max_score, 
    p.longest, 
    p.game_options, 
    u.first_name,
    dp.cnt AS game_played_count
FROM 
    ( SELECT game_options,
             COUNT(*) AS cnt
      FROM played
      GROUP BY game_options
    ) AS dp
  INNER JOIN played AS p
    ON  p.play_id =                         -- assuming that `play_id` is the PK
    ( SELECT pi.play_id
      FROM played AS pi
      WHERE pi.game_options = dp.game_options   
      ORDER BY pi.score DESC,               -- the predefined
               pi.play_id ASC               -- order
      LIMIT 1 
    ) 
  INNER JOIN users AS u
    ON  p.user_id = u.user_id ;

(game_options, score, play_id) 上的索引将有助于提高效率 - 在两个查询中。