ORDER BY MAX(`field_name`) Using GROUP BY

Question

我目前正在一个足球网站上工作，我正在开发的一个功能可以追溯到历史上，看看每年哪个球员进球最多。

我有一个可行的解决方案，但认为有一种更简单有效的方法。

以下是一些示例数据：

-----------------------
year | name    | goals
-----------------------
1  | player1 |  6
1  | player2 |  8
1  | player3 |  4
2  | player1 |  3
2  | player2 |  5
2  | player3 |  2
3  | player1 |  7
3  | player2 |  7
3  | player3 |  3

使用以下查询，我能够检索到所需的部分数据。

按年份从table组中选择year，MAX(goals)作为goals

------------
year | goals
------------
1  |  8
2  |  5
3  |  7

然而，从name列中检索正确的值更为困难，因为在第3年等情况下，我不得不使用GROUP_CONCAT，其中多个name等于goals的最大值。

目前，我正在使用两个查询来获得我想要的结果。我仍在使用上面的查询，但随后使用PHPwhile循环每年单独搜索。

这是我的代码：

$load_goals = $db->query("SELECT `year`, MAX(`goals`) AS `goals` FROM `table` GROUP BY `year` DESC);
while($goals = $load_goals->fetchObject())
{
$players = $db->prepare("SELECT `year`, GROUP_CONCAT(`name` ORDER BY `name`) AS `name`, `goals` FROM `table` WHERE `year` = :year AND `goals` = :goals");
}

我曾试图将这两个问题合并为一个问题，但没有成功，因此非常感谢这里的任何指导。

为了澄清，这应该是我的最终结果。请注意第三年发生了什么，GROUP_CONCAT已经在那里得到了服务。

------------------------------
year | name            | goals
------------------------------
1  | player2         |  8
2  | player2         |  5
3  | player1,player2 |  7

Answer 1

所以你想要所有进球数最多的球员。一种方法使用变量。另一个需要子查询。下面使用了一个相关的子查询，但有几种方法可以编写：

SELECT year, goals, GROUP_CONCAT(name ORDER BY name) as names
FROM table t
WHERE t.goals = (SELECT MAX(t2.goals) AS goals
FROM table t2
WHERE t2.year = t.year
)
GROUP BY year, goals;

Answer 2

只需将初始查询连接回原始表：

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
SELECT year, MAX(goals) AS goals
FROM yourTable
GROUP BY year
) t2
ON t1.year  = t2.year AND
t1.goals = t2.goals

使用这种方法，多年来会有多个记录显示，不止一名球员的进球数最多。例如，如果你对你的样本数据集运行这个查询，那么第三年的球员1和2都会出现，每个球员都有7个进球。