184. Department Highest Salary (leetcode)

https://leetcode.com/problems/department-highest-salary/

문제

Write an SQL query to find employees who have the highest salary in each of the departments.

Return the result table in any order.

The query result format is in the following example.

번역하자면, 각 부서에서 급여가 가장 높은 직원을 찾는 SQL 쿼리를 작성해야 하는 문제입니다.

Employee 테이블과 Department 테이블은 JOIN으로 합쳐줘야하는데요.

공통적으로 id 칼럼을 가지고 있기에 id 기준으로 FROM절에서 join을 해줍니다.

FROM employee join department ON employee.departmentId = department.id

WHERE절에서 서브쿼리를 이용해 부서에서 가장 급여가 높은 금액을 불러오겠습니다.

부서별로, 급여가 가장 높은 금액을 불러옵니다.

WHERE salary IN (SELECT max(salary) 
FROM employee join department ON employee.departmentId = department.id 
GROUP BY departmentId)

이제 SELECT 절에서 지정됨 컬럼을 불러오면 정답입니다.

SELECT department.name AS Department,employee.name AS Employee, Salary

정답

SELECT department.name AS Department,employee.name AS Employee, Salary
FROM employee join department ON employee.departmentId = department.id
WHERE salary IN (SELECT max(salary) 
from employee join department ON employee.departmentId = department.id 
GROUP BY departmentId)