为何我的MySQL查询无法返回完成最多挑战的所有黑客姓名？

你的查询失效的原因 #

1. 多余的外层分组：子查询已经通过GROUP BY Hackers.hacker_id,Hackers.name得到了每个黑客的挑战完成数，外层再按am.name,am.names_appeared分组完全没必要，反而会导致逻辑混乱。
2. HAVING子句中MAX()的用法错误：在当前的分组逻辑下，MAX(am.names_appeared)是每个分组内的最大值，而非整个结果集的全局最大值。这就导致am.names_appeared = MAX(am.names_appeared)对每一行都成立，自然会返回所有黑客的数据。

正确的查询写法 #

方法一：先计算全局最大挑战数，再筛选匹配记录

SELECT h.name, COUNT(c.challenge_id) AS challenge_count
FROM Hackers h
INNER JOIN Challenges c ON h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name
HAVING COUNT(c.challenge_id) = (
    -- 先算出所有黑客中完成挑战的最大数量
    SELECT MAX(count_val)
    FROM (
        SELECT COUNT(*) AS count_val
        FROM Challenges
        GROUP BY hacker_id
    ) AS counts
);

方法二：使用窗口函数（MySQL 8.0及以上版本支持）

窗口函数可以直接在子查询中计算全局最大挑战数，写法更简洁：

SELECT name, challenge_count
FROM (
    SELECT h.name, COUNT(c.challenge_id) AS challenge_count,
           -- 计算全局最大挑战数
           MAX(COUNT(c.challenge_id)) OVER () AS max_count
    FROM Hackers h
    INNER JOIN Challenges c ON h.hacker_id = c.hacker_id
    GROUP BY h.hacker_id, h.name
) AS sub
WHERE challenge_count = max_count;

额外提示 #

子查询中用Count(name)统计挑战数语义不准确，应该用COUNT(c.challenge_id)或者COUNT(*)，因为我们要统计的是每个黑客完成的挑战条目数，用挑战ID更贴合业务逻辑。

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