8/31 每日一題(MYSQL 查詢每組部門中薪水最高人員)

 

Table: Employee

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
id is the primary key (column with unique values) for this table.
departmentId is a foreign key (reference columns) 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 with unique values) 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 a solution to find employees who have the highest salary in each of the departments.

Return the result table in any order.

The 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.

# Write your MySQL query statement below

#返回部門最高薪水

SELECT

D.name AS Department ,#部門名稱

E.name AS Employee ,#員工名稱

E.salary AS Salary #薪水

FROM Employee AS E

JOIN Department AS D

ON E.departmentId =D.id #建立關聯資料

#----子查詢:篩選每個部門的最高薪水----

WHERE(E.departmentId ,E.salary)

IN (SELECT departmentId ,MAX(salary)

FROM Employee

GROUP BY departmentId) #按部門分組

#子查詢查詢departmentID 是因為要取得每個部門的的最高薪水

#然後在主查詢中WHERE 子句使用這些條件來選擇對應員工

#基本上 子查詢中的目標是建立一個對應每個部門的最高薪水以及部門ID的列表,

主查詢可以使用這個列表來來選族相關的員工資訊

---------------------------

---------------------------------------------

SELECT Department, Salary, Employee
FROM (SELECT e.name AS Employee 
            , salary AS Salary 
            , d.name  AS Department 
            , DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS rn
        FROM Employee e
            JOIN Department d ON e.departmentId = d.id) AS row_num
WHERE rn = 1