mysql - 当我 MAX(ColA) 和 GROUP BY ColB 时，如何从表中选择行

Question

我找到了 this question这非常相似，但我仍然遇到一些麻烦。

所以我从名为 Scores 的表开始

id | player | time | scoreA | scoreB |
~~~|~~~~~~~~|~~~~~~|~~~~~~~~|~~~~~~~~|
 1 | John   |  10  |   70   |   80   |
 2 | Bob    |  22  |   75   |   85   |
 3 | John   |  52  |   55   |   75   |
 4 | Ted    |  39  |   60   |   90   |
 5 | John   |  35  |   90   |   90   |
 6 | Bob    |  27  |   65   |   85   |
 7 | John   |  33  |   60   |   80   |

我想为每个玩家选择最佳平均分以及该记录中的信息。澄清一下，最佳平均分是 (scoreA + scoreB)/2 的最高值。

结果是这样的

id | player | time | scoreA | scoreB | avg_score |
~~~|~~~~~~~~|~~~~~~|~~~~~~~~|~~~~~~~~|~~~~~~~~~~~|
 5 | John   |  35  |   90   |   90   |    90     |
 2 | Bob    |  22  |   75   |   85   |    80     |
 4 | Ted    |  39  |   60   |   90   |    75     |

根据我上面链接的问题，我尝试了这样的查询，

SELECT
   s.*,
   avg_score
FROM
   Scores AS s 
INNER JOIN (
   SELECT
      MAX((scoreA + scoreB)/2) AS avg_score,
      player,
      id
   FROM
      Scores
   GROUP BY
      player
) AS avg_s ON s.id = avg_s.id
ORDER BY
   avg_score DESC,
   s.time ASC

这实际上给我的是，

id | player | time | scoreA | scoreB | avg_score |
~~~|~~~~~~~~|~~~~~~|~~~~~~~~|~~~~~~~~|~~~~~~~~~~~|
 1 | John   |  10  |   70   |   80   |    90     |
 2 | Bob    |  22  |   75   |   85   |    80     |
 4 | Ted    |  39  |   60   |   90   |    75     |

如您所见，它从记录 5 中获得了正确的最大 avg_score，但从另一条记录(记录 1)中获得了其余信息。我错过了什么？如何确保数据全部来自同一条记录？我得到了正确的 avg_score 但我想要与该记录关联的其余数据，在本例中为记录 5。

提前致谢!

Answer 1

 SELECT x.*
      , (scoreA+scoreB)/2 avg_score 
   FROM scores x 
   JOIN
      ( SELECT player, MAX((scoreA+scoreB)/2) max_avg_score FROM scores GROUP BY player) y 
     ON y.player = x.player 
    AND y.max_avg_score = (scoreA+x.scoreB)/2;