LeetCode MySQL-184. Department Highest Salary

    AllenLi
    LeetCode MySQL

    Write an SQL query to find employees who have the highest salary in each of the departments.
    Return the result table in any order.

    寫一個 SQL Query來尋找每個部門中薪水最高的員工。
可任意排序結果。

    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.

    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.

    Solution:
    1. 選擇標題 t2.name 為 Department，t1.name 為 Employee，t1.salary 為 Salary。
    2. 分別建立 Employee 為 t1，Department 為 t2。
    3. 以部門id為基準，尋找 salary 最高的員工。

    Code.1:

    SELECT t2.name AS Department,
	t1.name As Employee,
    t1.salary As Salary
FROM Employee AS t1
LEFT JOIN Department AS t2
	ON t1.departmentId = t2.id
WHERE (t2.id, t1.salary) IN 
	(SELECT departmentId,
		MAX(salary)
        FROM Employee
        GROUP BY departmentId);

    newEmployee

    Related Posts