给出一个表Employees
+-------+--------+-----------+------+-----------+------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+-----------+------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 17-Dec-80 | 800 | | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 20-Feb-81 | 1600 | 300 | 30 | | 7521 | WARD | SALESMAN | 7698 | 22-Feb-81 | 1250 | 500 | 30 | | 7566 | JONES | MANAGER | 7839 | 02-Apr-81 | 2975 | | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 28-Sep-81 | 1250 | 1400 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 01-May-81 | 2850 | | 30 | | 7782 | CLARK | MANAGER | 7839 | 09-Jun-81 | 2450 | | 10 | | 7788 | SCOTT | ANALYST | 7566 | 19-Apr-87 | 3000 | | 20 | | 7839 | KING | PRESIDENT | null | 17-Nov-81 | 5000 | | 10 | | 7844 | TURNER | SALESMAN | 7698 | 08-Sep-81 | 1500 | 0 | 30 | | 7876 | ADAMS | CLERK | 7788 | 23-May-87 | 1100 | | 20 | | 7900 | JAMES | CLERK | 7698 | 03-Dec-81 | 950 | | 30 | | 7902 | FORD | ANALYST | 7566 | 03-Dec-81 | 3000 | | 20 | | 7934 | MILLER | CLERK | 7782 | 23-Jan-82 | 1300 | | 10 | +-------+--------+-----------+------+-----------+------+------+--------+
和另一个表部门
+--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+
我怎样才能找到每个部门的最高工资？
输出格式行是：
DEPTNO,ENAME,DNAME,SAL,LOC
解决方法
您可以使用将获得每个部门的max(薪水)的子查询：
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.dname
) de
on e.sal = de.MaxSalary
and e.deptno = de.deptno
order by de.deptno
请参阅SQL Fiddle with Demo.您会注意到,这会为deptno = 20返回2行,因为有两个员工具有相同的薪水.
如果您使用的是具有窗口函数的数据库,那么您将需要使用dense_rank(),这样您将返回在每个部门中具有最高薪水的所有员工：
select d.deptno,d.dname,d.loc from ( select e.ename,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;
两个版本的结果是：
| 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 |
