MAX函数与GROUP BY子句一起使用

首先,您必须获得每个部门的最高薪水:

SELECT department, max(salary) as max_salary
FROM test
GROUP BY department

然后你可以将这个子查询加入到测试表中:

SELECT t.*
FROM
  test t INNER JOIN (
    SELECT department, max(salary) as max_salary
    FROM test
    GROUP BY department
  ) d ON t.department=d.department AND t.salary=d.max_salary