SQL 如何选择每个部门的最高工资，包括获得它的员工
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15189328/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select the max salary per department, including the employees that earn it
提问by user2129466
Given a table Employees
给定一张桌子 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 |
+-------+--------+-----------+------+-----------+------+------+--------+
And another table Departments
还有一张桌子 Departments
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
How can I find the maximum salary of each department?
如何找到每个部门的最高工资？
The output format line is:
输出格式行是：
DEPTNO,ENAME,DNAME,SAL,LOC
回答by Gordon Linoff
If you want to find the maximum salary along with the employees, then use the ANSI standard row_number()function:
如果您想与员工一起找到最高工资，请使用 ANSI 标准row_number()函数：
select d.deptno, e.ename, d.dname, e.sal, d.loc
from (select e.*, row_number() over (partition by deptno order by sal desc) as seqnum
from employees e
) e join
departments d
on e.deptno = d.deptno
where seqnum = 1
回答by Taryn
You can use a subquery that will get the max(salary)for each department:
您可以使用将获得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 Fiddle with Demo. You will notice that this returns 2 rows for deptno=20because there are two employees that have the same salary.
请参阅SQL Fiddle with Demo。您会注意到这将返回 2 行，deptno=20因为有两个员工的薪水相同。
If you are using a database that has windowing functions, then you will want to use dense_rank()so then you will return all employees who have the maximum salary in each department:
如果您正在使用具有窗口函数的数据库，那么您将想要使用dense_rank()so 那么您将返回每个部门中拥有最高薪水的所有员工：
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 Fiddle with Demo.
The result of both versions is:
两个版本的结果是：
| 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 |
