[184] Department Highest Salary

1. 문제 설명 

table : Employee

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
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

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id is the primary key column for this table.
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:

Input: 
Employee table:
+----+-------+--------+--------------+
| id | name  | salary | departmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name  |
+----+-------+
| 1  | IT    |
| 2  | Sales |
+----+-------+
Output: 
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
| IT         | Max      | 90000  |
+------------+----------+--------+
Explanation: Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.

 

2. 문제 풀이 

 

3. 정답

SUB-QUERY

-- 1) FROM 절 서브쿼리
SELECT df.name as Department
     , e.name as Employee
     , e.salary as Salary
FROM (
    SELECT d.name
         , d.id
         , max(e.salary) as salary
    FROM employee e
         INNER JOIN department d ON e.departmentid = d.id
    GROUP BY d.name, d.id
    ) df INNER JOIN employee e ON e.salary = df.salary AND e.departmentid = df.id


-- 2) 다중 컬럼 서브쿼리 
-- where절에 서브쿼리 넣어두기 
SELECT d.name   AS 'department',
       e.name   AS 'employee',
       salary
FROM   employee e
JOIN department d
         ON e.departmentid = d.id
WHERE  (e.departmentid, salary ) IN (SELECT departmentid,
                                         Max(salary)
                                      FROM   employee
                                      GROUP  BY departmentid);

 

WINDOW FUNCTION 

# WINDOW FUNCTION _ MAX 

SELECT ms.department AS Department
	, ms.name AS Employee
    , ms.salary AS Salary	
FROM (
	SELECT employee.name
    	, employee.salary
        , department.name AS department
        , MAX(salary) OVER (PARTITION BY departmentId) max_salary
    FROM employee
    	INNER JOIN department ON employee.departmentId = department.Id) ms
WHERE ms.salary = ms.max_salary

# WINDOW FUCNTION _ RANK( )

# WINDOW FUCNTION _ DENSE_RANK( )