184. Department Highest Salary (INNER JOIN, 서브쿼리, PARTITION BY)

[문제]

 

문제 링크

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

 

 

Table: Employee

id is the primary key column for this table.
departmentId is a foreign key of the ID from the Department table.
Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department.

 

Table: Department

id is the primary key column for this table. It is guaranteed that department name is not NULL.
Each row of this table indicates the ID of a department and its name.
 
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.

Example 1:

Explanation: Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.

 

 

[문제 설명 및 풀이 방법]

Department에서 가장 높은 Salary를 받는 사람의 Department, Employee, Salary를 구하기

 

 

---

[나의 풀이]

SELECT d.name AS Department
      ,e.name AS Employee
      ,e.salary As Salary
FROM employee e
-- 2. INNER JOIN 이용하여 departmentId가 같은 값, salary와 max_salary가 같은 값 찾기
  INNER JOIN 
-- 1. departmentid 별로 salary의 최대값 구하기
  (SELECT max(salary) AS max_salary
         ,departmentId
    FROM employee
    GROUP BY departmentId) AS dm
    ON dm.departmentId = e.departmentId AND e.salary = dm.max_salary
-- 3. INNER JOIN 이용하여 departmentId가 같은 값 찾아서 부서명 구하기
  INNER JOIN department d ON d.id = e.departmentId

---

[설명]

1. departmentid 별로 salary의 최대값 구하기

SELECT max(salary) AS max_salary
         ,departmentId
    FROM employee
    GROUP BY departmentId

출력 결과

 

 

2. INNER JOIN 이용하여 departmentId가 같은 값,  salary와 max_salary가 같은 값 찾기

SELECT e.name AS Employee
      ,e.salary As Salary
      ,e.departmentId
FROM employee e
  INNER JOIN 
  (SELECT max(salary) AS max_salary
         ,departmentId
    FROM employee
    GROUP BY departmentId) AS dm
    ON dm.departmentId = e.departmentId AND e.salary = dm.max_salary

출력 결과

 

3. INNER JOIN 이용하여 departmentId가 같은 값 찾아서 부서명 구하기

SELECT d.name AS Department
      ,e.name AS Employee
      ,e.salary As Salary
FROM employee e
  INNER JOIN 
  (SELECT max(salary) AS max_salary
         ,departmentId
    FROM employee
    GROUP BY departmentId) AS dm
    ON dm.departmentId = e.departmentId AND e.salary = dm.max_salary
  INNER JOIN department d ON d.id = e.departmentId

출력 결과

 

 

---

 

[다른 풀이]

-- 1. PARTITION BY를 이용하여 salary의 최대값 구하기
WITH max_employee AS
 (SELECT e.id AS id
    , e.name AS Employee 
    , e.salary AS Salary
    , d.name AS Department 
    , MAX(e.salary) OVER (PARTITION BY d.name) AS max_salary
FROM employee e
    INNER JOIN department d ON e.departmentid = d.id
 )

-- 2.max_salary와 같은 값을 갖는 salary 구하기
 SELECT Department 
        , Employee 
        , Salary
 FROM max_employee
 WHERE salary = max_salary

---

[설명]

1. PARTITION BY를 이용하여 salary의 최대값 구하기

WITH max_employee AS
 (SELECT e.id AS id
    , e.name AS Employee 
    , e.salary AS Salary
    , d.name AS Department 
    , MAX(e.salary) OVER (PARTITION BY d.name) AS max_salary
FROM employee e
    INNER JOIN department d ON e.departmentid = d.id
 )

 SELECT *
 FROM max_employee

출력 결과

MAX(e.salary) OVER (PARTITION BY d.name) AS max_salary

> d.name(Department)별로 salary가 max값을 구하여 max_salary에 출력하기

(GROUP BY는 그룹별로 요약하여 나타내고

PARTITION BY는 그룹별로 요약하지 않고(기존의 컬럼은 바뀌지 않고) 새로운 컬럼에 값이 출력된다.)

Department가 IT인 salary의 최대값은 9000이므로 Department가 IT인 max_salary에 9000이 출력된다.

Department가 Sales인 salary의 최대값은 8000이므로 Department가 Sales인 max_salary에 8000이 출력된다.

 

 

2. max_salary와 같은 값을 갖는 salary 구하기

WITH max_employee AS
 (SELECT e.id AS id
    , e.name AS Employee 
    , e.salary AS Salary
    , d.name AS Department 
    , MAX(e.salary) OVER (PARTITION BY d.name) AS max_salary
FROM employee e
    INNER JOIN department d ON e.departmentid = d.id
 )

 SELECT Department 
        , Employee 
        , Salary
 FROM max_employee
 WHERE salary = max_salary

출력 결과