[SQL] LeetCode: 184. Department Highest Salary

184. 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.

 

 

# 부서별로 가장 높은 연봉을 받는 직원 출력 

   똑같은 연봉을 받는 직원이 여러명이면 모두 출력해야 함.

 

 

나의 풀이 : JOIN 후 WHERE절 서브쿼리 이용

SELECT d.name AS Department 
      ,e.name AS Employee
      ,e.salary AS Salary 
FROM employee e
    INNER JOIN department d ON e.departmentid = d.id
WHERE (e.departmentid, e.salary) IN (SELECT departmentid
                                             ,MAX(salary)
                                     FROM employee 
                                     GROUP BY departmentid)

 

 

+) 다른 풀이: FROM절 서브쿼리 이용( 서브쿼리로 만든 테이블을 JOIN) 

 

SELECT d.name AS Department 
      ,e.name AS Employee
      ,e.salary AS Salary 
FROM employee e
    INNER JOIN( 
        SELECT departmentid ,MAX(salary) AS max_salary
        FROM employee 
        GROUP BY departmentid
        ) dh ON e.departmentid = dh.departmentid
             AND e.salary = dh.max_salary
    INNER JOIN department d ON e.departmentid = d.id