LeetCode > 184. Department Highest Salary

Jihyun Park·2020년 9월 6일
0
post-thumbnail

184. Department Highest Salary

Problem

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, your SQL query should return the following rows (order of rows does not matter).
문제링크

Answer

1. INNER JOIN과 서브쿼리로 풀기

SELECT  d.name as Department
      , e.name as Employee
      , e.salary
FROM employee as e
    INNER JOIN (
        SELECT DepartmentID, MAX(salary) as max_salary -- INNER JOIN 안에 서브쿼리
        FROM employee as e
        GROUP BY DepartmentID
        ) AS b ON b.DepartmentID = e.DepartmentID 
               AND b.max_salary = e.salary -- 두가지 조건 모두 만족
    INNER JOIN Department AS d ON e.departmentid = d.id -- INNER JOIN 두번

2. 윈도우 함수와 서브쿼리로 풀기

-- 한번에 통과했다! 이제 서브쿼리가 좀 익숙해지는 듯
SELECT Department.name AS Department
     , m.Name AS Employee
     , m.Maxsalary AS Salary
FROM (
    SELECT *
      , MAX(salary) OVER (PARTITION BY DepartmentID) AS MaxSalary -- MAX를 depatmentID 기준으로 윈도우함수 이용함
    FROM employee as e
     ) m
     INNER JOIN Department ON m.DepartmentId = Department.ID
WHERE m.Salary = m.Maxsalary

0개의 댓글