SQL 从每个部门选择具有最大 SAL 的 EMP
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16428473/
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
Select EMP with max SAL from each DEPT
提问by alejandromav
I′m having a bad time with a SQL query. I′m using oracle default tables:
我在使用 SQL 查询时遇到了麻烦。我正在使用 oracle 默认表：
'EMP' TABLE
“EMP”表
http://imageshack.us/photo/my-images/850/sinttuloxps.png/
http://imageshack.us/photo/my-images/850/sinttuloxps.png/
AND
和
'DEPT' TABLE
“部门”表
http://imageshack.us/photo/my-images/19/assayp.png/
http://imageshack.us/photo/my-images/19/assayp.png/
I wan′t to get the employee with the highest salary in each department.
我不想得到每个部门薪水最高的员工。
I′m using SQLPLUS.
我正在使用 SQLPLUS。
Thanks in advance!
提前致谢！
ALSO CODE:
还代码：
CREATE TABLE dept (
deptno numeric(2,0) NOT NULL,
dname character varying(14),
loc character varying(13),
CONSTRAINT dept_pkey PRIMARY KEY (deptno)
);
CREATE TABLE emp (
empno numeric(4,0) NOT NULL,
ename character varying(10),
job character varying(9),
mgr numeric(4,0),
hiredate date,
sal numeric(7,2),
comm numeric(7,2),
deptno numeric(2,0),
CONSTRAINT emp_pkey PRIMARY KEY (empno),
CONSTRAINT emp_deptno_fkey FOREIGN KEY (deptno) REFERENCES dept(deptno),
CONSTRAINT emp_mgr_fkey FOREIGN KEY (mgr) REFERENCES emp(empno)
);
INSERT INTO dept (deptno, dname, loc) VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept (deptno, dname, loc) VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept (deptno, dname, loc) VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept (deptno, dname, loc) VALUES (40, 'OPERATIONS', 'BOSTON');
insert into emp values (7839, 'KING' , 'PRESIDENT', NULL, to_date('17/11/81','dd/mm/yy') , 5000, NULL, 10);
insert into emp values (7566, 'JONES' , 'MANAGER' , 7839, to_date('02/04/81','dd/mm/yy') , 2975, NULL, 20);
insert into emp values (7902, 'FORD' , 'ANALYST' , 7566, to_date('03/12/81','dd/mm/yy') , 3000, NULL, 20);
insert into emp values (7369, 'SMITH' , 'CLERK' , 7902, to_date('17/12/80','dd/mm/yy') , 800, NULL, 20);
insert into emp values (7698, 'BLAKE' , 'MANAGER' , 7839, to_date('01/05/81','dd/mm/yy') , 2850, NULL, 30);
insert into emp values (7499, 'ALLEN' , 'SALESMAN' , 7698, to_date('20/02/81','dd/mm/yy') , 1600, 300, 30);
insert into emp values (7521, 'WARD' , 'SALESMAN' , 7698, to_date('22/02/81','dd/mm/yy') , 1250, 500, 30);
insert into emp values (7654, 'MARTIN' , 'SALESMAN' , 7698, to_date('28/09/81','dd/mm/yy') , 1250, 1400, 30);
insert into emp values (7782, 'CLARK' , 'MANAGER' , 7839, to_date('09/06/81','dd/mm/yy') , 2450, NULL, 10);
insert into emp values (7788, 'SCOTT' , 'ANALYST' , 7566, to_date('09/12/82','dd/mm/yy') , 3000, NULL, 20);
insert into emp values (7844, 'TURNER' , 'SALESMAN' , 7698, to_date('08/09/81','dd/mm/yy') , 1500, 0, 30);
insert into emp values (7876, 'ADAMS' , 'CLERK' , 7788, to_date('12/01/83','dd/mm/yy') , 1100, NULL, 20);
insert into emp values (7900, 'JAMES' , 'CLERK' , 7698, to_date('03/12/81','dd/mm/yy') , 950, NULL, 30);
insert into emp values (7934, 'MILLER' , 'CLERK' , 7782, to_date('23/01/82','dd/mm/yy') , 1300, NULL, 10);
回答by Ed Gibbs
You can also use the analytical RANK() function:
您还可以使用分析 RANK() 函数：
SELECT * FROM (
SELECT
Dept.DeptNo,
Dept.DName,
Emp.EName,
Emp.Sal,
RANK() OVER (PARTITION BY Dept.DeptNo ORDER BY Emp.Sal DESC) AS DeptSalRank
FROM Emp
INNER JOIN Dept ON Emp.DeptNo = Dept.DeptNo
)
WHERE DeptSalRank = 1
回答by Adriano Carneiro
Classic greatest-n-per-groupquery. Here is what you want:
经典的每组最大 n查询。这是你想要的：
select dept.dname, emp.empno, emp.ename, emp.sal
from emp
inner join dept on emp.deptno = dept.deptno
inner join
(
select emp.deptno, max(emp.sal) sal
from emp
group by emp.deptno
) ss on emp.deptno = ss.deptno and emp.sal = ss.sal
order by emp.sal desc
Here is a working fiddle: http://sqlfiddle.com/#!4/7147b/6
这是一个工作小提琴：http://sqlfiddle.com/#!4/ 7147b/6
Additionally, you might want to checkout a different approach. Look here (SQL Select only rows with Max Value on a Column) to see an interesting answer on the topic.
此外，您可能想要检查不同的方法。看这里（SQL Select only rows with Max Value on a Column）以查看有关该主题的有趣答案。
回答by Rakesh Anand
The following query will omit duplicate values
以下查询将省略重复值
SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO;
The following query will include the duplicate values
以下查询将包含重复值
SELECT * FROM EMP WHERE (DEPTNO,SAL) IN
( SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO)
回答by Saurabh Kumar Sarkar
As short as the question: SELECT DeptID, MAX(Salary) FROM Employees GROUP BY DeptID
就像问题一样简短： SELECT DeptID, MAX(Salary) FROM Employees GROUP BY DeptID
