[HackerRank] Top Earners / N가지 풀이

GROUP BY, ORDER BY, LIMIT 이용

SELECT 
    months * salary AS earnings,
    COUNT(employee_id)  AS cnt
FROM Employee
GROUP BY earnings
ORDER BY earnings DESC
LIMIT 1

 

WHERE절 서브쿼리 이용

earnings의 값이 max_earnings와 동일한 경우를 WHERE절 서브쿼리를 통해 필터링

SELECT months * salary AS earnings,
    COUNT(name)
FROM employee
WHERE months * salary = (SELECT MAX(months * salary) FROM employee) -- SELECT의 AS 쓸 수 없음
GROUP BY salary -- SELECT의 AS 쓸 수 있음

 

HAVING절 서브쿼리 이용

GROUP BY 결과를 필터링하고싶을 때 HAVING 사용

SELECT months * salary AS earnings,
    COUNT(*)
FROM employee
GROUP BY earnings
HAVING earnings = (SELECT MAX(months * salary) FROM employee)