인프런 커뮤니티 질문&답변
작성한 질문수
Challenges 질문입니다.
작성
·
199
1
SELECT H.hacker_id as hacker_id ,H.name as name ,COUNT(challenge_id) AS challenges_created
FROM Hackers AS H
INNER JOIN Challenges AS C ON H.hacker_id = C.hacker_id
GROUP BY hacker_id,H.name
HAVING COUNT(challenge_id) = (SELECT count(challenge_id)
FROM Challenges
GROUP BY hacker_id
ORDER BY count(challenge_id) DESC
Limit 1)
OR
COUNT(challenge_id) IN ((
SELECT small.number
FROM(
SELECT count(C.challenge_id) as number , H.name as name
FROM Hackers as H
INNER JOIN Challenges AS C ON H.hacker_id = C.hacker_id
GROUP BY H.name,H.hacker_id
) AS small
GROUP BY small.number
HAVING count(small.name) =1 ))
ORDER BY COUNT(challenge_id) DESC,hacker_id
-----------------------------------------
OR
COUNT(challenge_id) IN ((
SELECT small.number
FROM(
SELECT count(C.challenge_id) as number , H.name as name
FROM Hackers as H
INNER JOIN Challenges AS C ON H.hacker_id = C.hacker_id
GROUP BY H.name,H.hacker_id
) AS small
GROUP BY small.number
HAVING count(small.name) =1 ))
이 부분에서 값을 받아오지 못하는데 잘 모르겠습니다.
답변 3
1
안녕하세요 님, 답변드립니다.
말씀해주신 내용을 확인해보니, COUNT(challenge_id) 로 적어주신 코드는 표준 문법에 어긋 나지 않으며, mysql 인터프리터가 아닌 ms sql 인터프리터에서는 정상적으로 실행이 되고 있습니다. mysql 자체의 문제인지 혹은 해커랭크 mysql 인터프리터의 버그인지는 알 수 없으나, 처음 적어주신 코드도 문법적으로는 오류가 없음을 말씀드립니다. (참고: [MySQL Tutorial] MySQL HAVING)
답변이 도움이 되었길 바라며, 추가로 궁금한 점이 있으실 경우 언제든 문의 남겨주세요 :)
0
SELECT H.hacker_id as hacker_id ,H.name as name ,COUNT(challenge_id) AS challenges_created
FROM Hackers AS H
INNER JOIN Challenges AS C ON H.hacker_id = C.hacker_id
GROUP BY hacker_id,H.name
HAVING COUNT(challenge_id) = (SELECT count(challenge_id)
FROM Challenges
GROUP BY hacker_id
ORDER BY count(challenge_id) DESC
Limit 1)
OR /* COUNT(challenge_id) -> challenges_created 하면 해결*/
COUNT(challenge_id) IN ((
SELECT small.number
FROM(
SELECT count(C.challenge_id) as number , H.name as name
FROM Hackers as H
INNER JOIN Challenges AS C ON H.hacker_id = C.hacker_id
GROUP BY H.name,H.hacker_id
) AS small
GROUP BY small.number
HAVING count(small.name) =1 ))
ORDER BY COUNT(challenge_id) DESC,hacker_id
----------------------------------
이 부분이 문제였는데 무슨 차이인가요 ??
OR /* COUNT(challenge_id) -> challenges_created 하면 해결*/
COUNT(challenge_id) IN ((
SELECT small.number
FROM(
SELECT count(C.challenge_id) as number , H.name as name
FROM Hackers as H
INNER JOIN Challenges AS C ON H.hacker_id = C.hacker_id
GROUP BY H.name,H.hacker_id
) AS small
GROUP BY small.number
HAVING count(small.name) =1 ))
ORDER BY COUNT(challenge_id) DESC,hacker_id
0