Question

This question already has answers here:

SQL select only rows with max value on a column [duplicate] (27 answers)

Closed 11 hours ago.

This post was edited and submitted for review 11 hours ago.

我想筛选出每个部门薪水最高的记录。如果一个部门中有多条记录符合此条件，请将其全部保留。然后我编写SQL如下：

SELECT id, name, salary, departmentId
  FROM Employee
  GROUP BY departmentId
  HAVING salary = MAX(salary)

但它不能正常工作。

员工表：

+----+-------+--------+--------------+
| id | name  | salary | departmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

id is the primary key column for this table.

执行的结果是：

| id | name  | salary | departmentId |
| -- | ----- | ------ | ------------ |
| 3  | Henry | 80000  | 2            |

但我预期的结果是这样的：

| id | name  | salary | departmentId |
| -- | ----- | ------ | ------------ |
| 2  | Jim   | 90000  | 1            |
| -- | ----- | ------ | ------------ |
| 3  | Henry | 80000  | 2            |
| -- | ----- | ------ | ------------ |
| 5  | Max   | 90000  | 1            |

I want to know why I can t get the result that I expect? what s wrong with my SQL? I know how to write the correct SQL statement now. I just want to know what exactly is wrong with my SQL statement. Analysis of cause is more important for me.

Answer 1

您可以使用子查询来查找部门的最高工资。

SELECT 
  * 
FROM
  `Employee` e 
WHERE e.`salary` = 
  (SELECT 
    MAX(e1.`salary`) 
  FROM
    `Employee` e1 
  WHERE e1.`departmentId` = e.`departmentId`)

或者

SELECT e.id, e.name, e.salary, e.departmentId
FROM Employee e
INNER JOIN (
  SELECT departmentId, MAX(salary) AS max_salary
  FROM Employee
  GROUP BY departmentId
) subquery
ON e.departmentId = subquery.departmentId AND e.salary = subquery.max_salary;

This answer also works for MySQL 5.6 If you are using MySQL 8.X, you have your answer already in the comment section

Answer 2

select 
se.id, 
se.name, 
se.salary, 
se.department_id 
from stack_employees as se
join (
    select 
    department_id, 
    max(salary) as max_salary 
    from stack_employees
    group by department_id) di
on se.department_id = di.department_id
and se.salary = di.max_salary;

这将为您提供所需的输出。

友情链接