Department Highest Salary(Leet Code)

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

Department Highest Salary - LeetCode

 

 

문제

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.

 

 

정답쿼리1

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 (d.name, e.salary) IN(
                            SELECT d.name AS Department,
                                   MAX(salary) AS Salary
                            FROM Employee e
                            INNER JOIN Department d ON e.departmentId = d.id 
                            GROUP BY d.name)

 

해설1

서브쿼리로 부서의 이름과 부서별로 가장 많이 받는 연봉을 표시하는 다중컬럼을 만든다.

WHERE절 다중컬럼 서브쿼리 사용해서 서브쿼리에 해당하는 모든 값을 row들을 표시한다.

 

 

정답쿼리2

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