難度：Medium
| 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.
id為該表的主鍵（具有唯一值的列），
DepartmentId 是 Department 表中 ID 的外鍵（引用列）。
該表的每一行表示員工的 ID、姓名和薪水。它還包含其部門的 ID。
| Department表格屬性 |
| Column Name |
Type |
| id |
name |
| 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
id為該表的主鍵（具有唯一值的列），部門名稱不為Null，該表的每一行顯示部門的id及名稱。
原文：
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
個人翻譯（並非逐字翻譯）：
編寫一個解決方案，查找每個部門中薪水最高的員工。以任意順序顯示出查詢結果。
.
輸入：
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:
輸出：
| 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.
Max和Jimy在IT部門的薪資最高，Henry在銷售部門的薪資最高。
| 個人理解 |
這邊使用Left Join語法將兩個資料表組合在一起，再使用Max語法找出最大數值，再使用Where把最大數值挑出來，就會剩下第二高的數值。
這裡著重於使用Where（columns name）In（要選擇的值）、Max函數，以及雙層Select，這裡個人理解成類似巢狀迴圈的模式。 |
| 程式碼 |
select Department.name as Department, Employee.name as Employee, Employee.Salary as Salary
from Employee left join Department #inner join也可 on Employee.departmentId = Department.id where (Department.id, Employee.Salary) in ( select departmentId, max(Salary) from Employee group by departmentId ) #where (COLUMNS) in (需要放的值)0 |
LINE