MySQL报错ERROR 1111 Invalid use of group function如何修复

• 报错ERROR 1111 (HY000) Invalid use of group function确实出自你标注的代码行，问题在于MySQL不支持嵌套使用聚合函数：count(challenge_id)是分组后的聚合结果，不能直接在外层再套max()做二次聚合，这种写法不符合SQL语法规范。

把嵌套聚合的逻辑拆分为两层子查询：先统计每个用户的挑战总数，再对这个统计结果取最大值即可，同时优化了原代码的筛选逻辑，完全匹配题目要求，修正后的完整代码如下：

SELECT 
    h.hacker_id, 
    h.name, 
    COUNT(c.challenge_id) AS total
FROM Challenges c
INNER JOIN Hackers h ON h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name
HAVING total NOT IN (
    -- 筛选出需要排除的挑战数：出现次数>1且不是最大值
    SELECT cnt
    FROM (
        SELECT COUNT(challenge_id) AS cnt
        FROM Challenges
        GROUP BY hacker_id
    ) AS challenge_counts
    GROUP BY cnt
    HAVING COUNT(*) > 1 
    AND cnt != (
        -- 拆分嵌套聚合，先统计所有用户的挑战数再取最大值
        SELECT max(cnt)
        FROM (
            SELECT COUNT(challenge_id) AS cnt
            FROM Challenges
            GROUP BY hacker_id
        ) AS max_count
    )
)
ORDER BY total DESC, h.hacker_id;

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