Hackerrank Challenges问题：为何查询返回唯一的挑战计数结果？

这是HackerRank平台上Challenges题目的一个解决方案：

SELECT 
    c.hacker_id, h.name, COUNT(c.challenge_id) AS cnt 
FROM 
    Hackers AS h 
JOIN 
    Challenges AS c ON h.hacker_id = c.hacker_id
GROUP BY 
    c.hacker_id, h.name 
HAVING 
    cnt = (SELECT COUNT(c1.challenge_id) 
           FROM Challenges AS c1 
           GROUP BY c1.hacker_id 
           ORDER BY count(*) DESC
           LIMIT 1)
    OR cnt NOT IN (SELECT COUNT(c2.challenge_id) 
                   FROM Challenges AS c2 
                   GROUP BY c2.hacker_id 
                   HAVING c2.hacker_id <> c.hacker_id)
ORDER BY 
    cnt DESC, c.hacker_id;

疑问点解析 #

我无法理解以下子句为何能筛选出唯一结果：

cnt NOT IN (SELECT count(c2.challenge_id) 
            FROM Challenges AS c2 
            GROUP BY c2.hacker_id 
            HAVING c2.hacker_id <> c.hacker_id)

尤其想搞懂HAVING c2.hacker_id <> c.hacker_id的作用机制。

子句作用拆解 #

1. 整体逻辑
   这个子查询的核心是生成「除当前主查询行对应的黑客外，所有其他黑客的挑战数量集合」，cnt NOT IN (...)则用于筛选出当前黑客的挑战数未被任何其他黑客重复的记录。

2. HAVING c2.hacker_id <> c.hacker_id的具体机制
   这里的c.hacker_id是外层主查询中当前分组的黑客ID（主查询已按c.hacker_id分组，每个分组对应一个黑客）。这个HAVING条件会在子查询的分组结果中，排除掉和当前主查询行相同的黑客分组。

举个实际例子：
假设主查询正在处理黑客A（hacker_id=1），他的挑战数是5。子查询会统计所有黑客的挑战数，但会剔除黑客A自己的分组，最终得到其他所有黑客的挑战数集合。如果5不在这个集合里，说明只有黑客A一人完成了5个挑战，这条记录就会被选中。

3. 结合主查询的完整逻辑
   主查询的HAVING条件是二选一：

• 要么当前黑客的挑战数等于所有黑客中的最大值（所有并列第一的黑客都会被保留）
• 要么当前黑客的挑战数是独一无二的，没有其他黑客和他的挑战数相同

这样就能精准满足题目的要求：保留挑战数最多的黑客，以及挑战数唯一的黑客，排除那些挑战数既非最大值、又和他人重复的记录。

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