您可以使用子查询来获取max(salary)对于每个部门：
select de.deptno,
e.ename,
de.dname,
e.sal,
de.loc
from employees e
inner join
(
select max(e.sal) MaxSalary, d.deptno, d.loc, d.dname
from employees e
inner join departments d
on e.deptno = d.deptno
group by d.deptno, d.loc, d.dname
) de
on e.sal = de.MaxSalary
and e.deptno = de.deptno
order by de.deptno
See SQL 摆弄演示 http://www.sqlfiddle.com/#!3/78f2b/16。您会注意到这会返回 2 行deptno=20因为有两名员工的工资相同。
如果您使用的数据库具有窗口函数，那么您将需要使用dense_rank()那么你将返回每个部门中工资最高的所有员工：
select d.deptno, e.ename, d.dname, e.sal, d.loc
from
(
select e.ename,
e.sal,
e.deptno,
dense_rank() over (partition by deptno order by sal desc) as salRank
from employees e
) e
inner join departments d
on e.deptno = d.deptno
where salRank = 1;
See SQL 摆弄演示 http://www.sqlfiddle.com/#!3/78f2b/22.
两个版本的结果都是：
| DEPTNO | ENAME | DNAME | SAL | LOC |
-------------------------------------------------
| 10 | KING | ACCOUNTING | 5000 | NEW YORK |
| 20 | SCOTT | RESEARCH | 3000 | DALLAS |
| 20 | FORD | RESEARCH | 3000 | DALLAS |
| 30 | BLAKE | SALES | 2850 | CHICAGO |
