[mysql] LeetCode 184.Department Highest Salary

184.Department Highest Salary

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

문제설명

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 theDepartmenttable.
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. It is guaranteed that department name is notNULL.
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.

---

풀이

SELECT D.name AS Department, E.name AS Employee , E.salary AS Salary
FROM Employee E
JOIN Department D
ON E.departmentId = D.id
WHERE (Salary, E.departmentId) IN (SELECT MAX(salary), departmentId
                  FROM Employee 
                  GROUP BY departmentId);

다른풀이

WITH
A AS(
    SELECT 
            departmentId
        ,   MAX(salary) AS max_sa
    FROM employee
    WHERE 1 = 1
    GROUP BY 1
)
,B AS(
    SELECT *
    FROM A
    LEFT JOIN employee USING(departmentId)
    WHERE 1 = 1
        AND salary = max_sa
        
)
SELECT t.name AS department, b.name AS employee, salary
FROM B
LEFT JOIN department T ON t.id = B.departmentId