MySQL 选择所有部门中工资最高的所有员工
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13582000/
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 all the employee in all departments which are having highest salary in that department
提问by user1817490
In one of the interviews one person asked me below question
在其中一次采访中，有人问了我以下问题
"Write a query to find out all employee in all departments which are having highest salary in that department with department name,employee name and his salary"
“写一个查询，用部门名称、员工姓名和他的薪水找出所有部门中工资最高的所有员工”
It means that there are 100 records in employee table and 10 records in department table. So it needs to give me 10 records from query plus if there is no employee in any department it still needs to show that department name.
这意味着employee表有100条记录，department表有10条记录。所以它需要从查询中给我 10 条记录，如果任何部门都没有员工，它仍然需要显示该部门名称。
Thanks
谢谢
采纳答案by Vahid Hallaji
This query gives you a list of departments, with that department's highest paid salary, if exists, or null otherwise. In this case, selecting the employee names do not give you the right name and just return the first employee in the linked department!
此查询为您提供部门列表，如果存在，则为该部门的最高薪水，否则为 null。在这种情况下，选择员工姓名不会为您提供正确的姓名，而只会返回链接部门中的第一个员工！
SELECT
d.name,
MAX(e.salary)
FROM
department d
LEFT OUTER JOIN employee e ON (e.department_id = d.id)
GROUP BY d.id
See on SQL Fiddle
请参阅SQL Fiddle
If you wish a list of departments with the highest salary and employee name:
如果您希望获得最高工资和员工姓名的部门列表：
SELECT
d.name,
e.name, e.salary
FROM
department d
LEFT OUTER JOIN employee e ON (e.department_id = d.id)
WHERE e.salary IN (
SELECT MAX(em.salary) FROM employee em
WHERE em.department_id = d.id
);
See on SQL Fiddle
请参阅SQL Fiddle
回答by Taryn
Without seeing a table structure, I would say that you could probably do this a few different ways.
在没有看到表结构的情况下，我会说您可能可以通过几种不同的方式来做到这一点。
Using an INclause:
使用IN子句：
select e.name e_name,
d.name d_name,
e.salary
from employee e
inner join department d
on e.deptid = d.id
where e.salary in (select max(salary)
from employee
group by deptid);
Or using a subquery:
或者使用子查询：
select e1.name e_name,
d.name d_name,
e1.salary
from employee e1
inner join
(
select max(salary) salary, deptid
from employee
group by deptid
) e2
on e1.salary = e2.salary
and e1.deptid = e2.deptid
inner join department d
on e1.deptid = d.id
See SQL Fiddle with Demoof both
请参阅SQL拨弄演示两个
Now, MySQL allows you to apply an aggregate function and not apply a GROUP BYto non-aggregated fields in the select list (this cannot be done in sql server, oracle, etc). So you could use to get the same result:
现在，MySQL 允许您应用聚合函数而不是将 a 应用GROUP BY到选择列表中的非聚合字段（这在 sql server、oracle 等中无法完成）。所以你可以用得到相同的结果：
select e.name e_name,
d.name d_name,
max(e.salary) salary
from employee e
inner join department d
on e.deptid = d.id
group by d.name
回答by Suman Singh
Table "emp" has
id, name,d_id,salary
and Table "department" has
id, dname
fields.
领域。
Below query will output higest salary with department name
下面的查询将输出带有部门名称的最高工资
SELECT E.id,
E.name,
D.dname,
max(E.salary) as higest_salary
FROM `emp` as E
left join department as D
on D.id=E.d_id
group by E.d_id
回答by Pushkar Jethwa
For SQL Server 2008 Not Best Solution...But Fully Working on HR Database Migrated from Oracle 10G
对于 SQL Server 2008 不是最好的解决方案……而是充分利用从 Oracle 10G 迁移的 HR 数据库
select e.DEPARTMENT_ID,d.MaxSalary,es.FIRST_NAME,dm.MinSalary,esd.FIRST_NAME
from EMPLOYEES e
join (select department_id,MAX(salary) MaxSalary from EMPLOYEES group by DEPARTMENT_ID) d
on e.DEPARTMENT_ID=d.DEPARTMENT_ID
join (select first_name,DEPARTMENT_ID from EMPLOYEES ess where SALARY in (select MAX(salary) from EMPLOYEES where DEPARTMENT_ID=ess.DEPARTMENT_ID)) es
on e.DEPARTMENT_ID=es.DEPARTMENT_ID
join (select department_id,min(salary) MinSalary from EMPLOYEES group by DEPARTMENT_ID) dm
on e.DEPARTMENT_ID=dm.DEPARTMENT_ID
join (select first_name,DEPARTMENT_ID from EMPLOYEES ess where SALARY in (select min(salary) from EMPLOYEES where DEPARTMENT_ID=ess.DEPARTMENT_ID )) esd
on e.DEPARTMENT_ID=esd.DEPARTMENT_ID
group by e.DEPARTMENT_ID,d.MaxSalary,es.FIRST_NAME,dm.MinSalary,esd.FIRST_NAME
回答by LaviJ
SELECT empname,MAX(salary) FROM employeeGROUP BY dep_id
从GROUP BY 中选择empname,MAX( salary)employeedep_id
Above query will generate an accurate result.
以上查询将生成准确的结果。
回答by silpa
select ename from emp where salary in (select max(salary) from emp group by department);
从 emp 中选择 ename，其中的薪水在（按部门从 emp 组中选择 max(salary)）；
