mysql - 使用 GROUP BY 从表中选择 MAX()

Question

我有一些表:

name    dep_id  salary
Vasia   5       1000
Oleg    5       1300
Vitia   4       1000
Sacha   3       1100
Kolia   5       1600
Lesha   2       1400
Sergey  4       1200
Marina  5       1300
Olga    5       1500
Igor    4       1400
Valia   3       1500
Erema   4       1500

我需要获取部门中薪水最高的员工的姓名

即我需要

 Lesha   1400
 Valia   1500
 Erema   1500
 Kolia   1600

我试过:

SELECT name, max(salary) FROM employees GROUP BY dep_id

但这会显示不正确的值

我该怎么做？

Answer 1

select e1.*
from employees e1
join
(
  SELECT dep_id, max(salary) as msal
  FROM employees
  GROUP BY dep_id
) e2 on e1.dep_id = e2.dep_id
    and e1.salary = e2.msal