---
# System prepended metadata

title: 184. Department Highest Salary
tags: [' LeetCode', sql]

---

---
tags: sql, LeetCode
disqus: HackMD
---

# 184. Department Highest Salary
(資訊來自於`leetcode 184`[Department Highest Salary](https://leetcode.com/problems/department-highest-salary/))
#### 抓取部門裡面最高的薪水
![](https://i.imgur.com/Vqce8QB.png)

這是一個叫做Employee的主Table，裡面的資料欄位有【id】,【name】,【salary】,【departmentId】
id 是該表的主鍵列。
departmentId 是Department 表中 ID 的外鍵。
該表的每一行表示員工的 ID、姓名和薪水。它還包含其部門的 ID。

![](https://i.imgur.com/uc7dfuT.png)
這是另一個子Table
id 是該表的主鍵列。
該表的每一行表示一個部門的 ID 和它的名稱。

## 題目:
編寫一個 SQL 查詢來查找每個部門中薪水最高的員工。

以任意順序返回結果表。

查詢結果格式如下例所示。

資料庫內容為下圖

![](https://i.imgur.com/Wzuipw0.png)

而我們希望出來的結果為下面這樣

![](https://i.imgur.com/hI1sqJL.png)

 Max和Jim都是IT部門薪水最高的，Henry在銷售部門中薪水最高。
 
 ## 解題方式:
 第一種解題方式
 ```sql=
 select A.name as Department,M.name as Employee,M.salary as Salary
from Employee M,(select departmentId,MAX(salary) max from Employee GROUP BY DepartmentId) T,Department A
where M.departmentId = T.departmentId
and M.salary = T.max 
and M.departmentId = A.id
```
 第二種解題方式
 ```sql=
 select A.name as Department,M.name as Employee,M.salary as Salary
from Employee M
left outer join Department A on M.departmentId = A.id 
left outer join (select departmentId,MAX(salary) max from Employee group by DepartmentId) T on 1=1
where M.departmentId = T.departmentId
and M.salary = T.max 
and M.departmentId = A.id
```

By. @OtbmOtOMRzCEOxuIjDNc8A 