此行的含义: HAVING c2.hacker_id <>c.hacker.id? [英] Meaning of this line: HAVING c2.hacker_id <> c.hacker.id?

问题描述

我一直在研究 HackerRank SQL 问题，挑战".

I've been working on a HackerRank SQL question, "Challenges".

这就是问题所在:

Julia 要求她的学生创造一些编码挑战.编写查询以打印每个学生创建的hacker_id、姓名和挑战总数.按降序按挑战总数对您的结果进行排序.如果不止一名学生创建了相同数量的挑战，则按hacker_id 对结果进行排序.如果不止一名学生创建了相同数量的挑战，并且计数小于创建的最大挑战数，则从结果中排除这些学生.

Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

这个问题难倒了我，我在网上搜索看看其他人是如何解决的，当我遇到这个Github 在这里，他提出了一个非常优雅的解决方案.

This question stumped me, and I was searching online to see how other people have solved it, when I came across this Github here, who proposed a really elegant solution.

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;

我理解海报如何确保我们在 HAVING 的第一个条件下只能获得最大数量的挑战，但至于第二部分，我真的不明白.我不确定为什么 HAVING 查询的第二个条件有效: HAVING c2.hacker_id <> c.hacker_id

I understand how the poster ensured that we'd only get the maximum number of challenges in the first condition of the HAVING, but as for the second part, I don't really understand. I'm not sure why the second condition of the HAVING query works: HAVING c2.hacker_id <> c.hacker_id

不会总是 c2.hacker_id = c.hacker_id 吗?

Won't c2.hacker_id = c.hacker_id always?

推荐答案

如果将它们相等 (c2.hacker_id = c.hacker_id)，结果与相同的 hacker_id 有关>.在不包括 hacker_id 本身的不等式条件下.这意味着计算 hacker_id 不等于外部查询的 hacker_id 的挑战数量，并将这些计数从主查询中排除.

if you equalize them (c2.hacker_id = c.hacker_id), the results relates with same hacker_id. on the inequality condition it counts by not including hacker_id itself. it means count number of challenges whose hacker_id is not equal to hacker_id of the outer query and exempt those counts from main query.

这篇关于此行的含义: HAVING c2.hacker_id &lt;&gt;c.hacker.id?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！