LeetCode 184. 部門最高賃金の従業員

Employee表には、対応するId、salary、department Idを持つすべての従業員情報が含まれています.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

Department表には、会社のすべての部門の情報が含まれています.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

SQLクエリーを作成し、各部門で最も給料の高い従業員を見つけます.例えば、上記の表によれば、MaxはIT部門で最高賃金、HenryはSales部門で最高賃金を有する.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

考え方1:連結表を通して、相応の最高賃金を判断する

select d.name as Department,e.name as Employee,e.Salary
from Department as d,Employee as e
where d.Id=e.DepartmentId and e.Salary=(Select max(Salary) from Employee where DepartmentId=d.Id)

考え方2:グループbyで各部門の最高賃金を調べて、連結表を通して

SELECT
d.`Name` AS Department ,
e.`Name` AS Employee ,
e.Salary
FROM (SELECT departmentId,max(Salary) AS Salary FROM employee GROUP BY DepartmentId) AS x,
employee AS e,department AS d
WHERE x.departmentId=e.DepartmentId and x.Salary=e.Salary and e.DepartmentId=d.Id