LC-mysql-184(DeparmentHighestSal

本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
)