SQL中max函数搭配group by统计最高进球球员结果错误如何解决？

问题原因 #

你当前的SQL用了max(goals)聚合函数，逻辑是统计每个球员的单场最高进球数，和你需要的「统计每个球员累计总进球、再取总进球最高的记录」需求完全不符。

解决方案 #

步骤1：先统计每个球员的总进球数

将max(goals)替换为sum(goals)，即可得到每个球员的累计总进球：

select player_id, sum(goals) as total_goals
from soccer.player_match_stat
group by player_id

运行该语句你会得到以下中间结果：

步骤2：筛选出总进球最高的记录

如果只需要取最高的1条（不处理并列最高的情况），直接排序后取第一条即可，通用适配所有SQL数据库的写法如下：

select player_id, total_goals
from (
    select player_id, sum(goals) as total_goals
    from soccer.player_match_stat
    group by player_id
) as player_total
order by total_goals desc
limit 1

如果需要处理多人总进球并列第一的场景，用以下写法：

select player_id, sum(goals) as total_goals
from soccer.player_match_stat
group by player_id
having sum(goals) = (
    select max(total_goals)
    from (
        select sum(goals) as total_goals
        from soccer.player_match_stat
        group by player_id
    ) as t
)

内容的提问来源于stack exchange，提问作者yasamingol