本SQL問題是 #184. Department Highest Salary
問題
編寫SQL，找出每個部門的最高薪資
Employee表如下
+----+-------+--------+--------------+
| 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 |
+----+----------+
思路
查找最高薪水：
SELECT MAX(e.Salary) as m,e.DepartmentId as d
FROM Employee as e
GROUP BY e.DepartmentId
結果：
+---------+-----+
| m | d |
+---------+-----+
| 90000 | 1 |
| 80000 | 2 |
+---------+-----+
再將最高薪水的記錄篩選出來
SELECT ee.Name,ee.Salary,ee.DepartmentId
FROM Employee as ee
WHERE (ee.Salary,ee.DepartmentId) IN(
SELECT MAX(e.Salary),e.DepartmentId
FROM Employee as e
GROUP BY e.DepartmentId)
此處重點在于
(ee.Salary,ee.DepartmentId) IN ()來進行篩選副女，之前都是ee.Salary IN ()思想都禁錮住了。
結果：
+-------+--------+--------------+
| Name | Salary | DepartmentId |
+-------+--------+--------------+
| Max | 90000 | 1 |
| Henry | 80000 | 2 |
+-------+--------+--------------+
最后和Department表關聯(lián)即可:
select d.Name as Department,
k.Name as Employee,
k.Salary as Salary
from Department as d
join(
select ee.Name,ee.Salary,ee.DepartmentId
from Employee as ee
WHERE (ee.Salary,ee.DepartmentId) IN (
select MAX(e.Salary),e.DepartmentId as m
from Employee as e
group by e.DepartmentId
)
)k ON d.Id = k.DepartmentId
結果：
+-----------+---------+--------+
| Department| Employee| Salary |
+-----------+---------+--------+
| IT | MAX | 90000 |
| Sales | Henry | 80000 |
+-----------+---------+--------+
方法二
直接把兩張表連接起來路狮，在
where條件中篩選出最高薪水
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.Salary = (
SELECT MAX(ee.Salary) from Employee as ee where ee.DepartmentId = d.Id
)
