本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表关联即可:
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
)
