mysql - 如何找到每个部门中薪水最高的员工？

Question

这个问题已经有答案了:

Get records with max value for each group of grouped SQL results (19 个回答)

已关闭 4 年前。

CREATE TABLE employees(id int AUTO_INCREMENT PRIMARY KEY, emp_name VARCHAR(10), dept VARCHAR(10), salary DECIMAL(8,2));

INSERT INTO employees (dept,emp_name,salary) VALUES('Engg','Sam',1000);
INSERT INTO employees (dept,emp_name,salary) VALUES('Engg','Smith',2000);
INSERT INTO employees (dept,emp_name,salary) VALUES('HR','Denis',1500);
INSERT INTO employees (dept,emp_name,salary) VALUES('HR','Danny',3000);
INSERT INTO employees (dept,emp_name,salary) VALUES('IT','David',2000);
INSERT INTO employees (dept,emp_name,salary) VALUES('IT','John',3000);
INSERT INTO employees (dept,emp_name,salary) VALUES('Sales','Ravi',4000);
INSERT INTO employees (dept,emp_name,salary) VALUES('Sales','Bob',6000);
INSERT INTO employees (dept,emp_name,salary) VALUES('HR','Rick',4500);
INSERT INTO employees (dept,emp_name,salary) VALUES('IT','Mathew',2500);

表格:员工

+----+----------+-------+---------+
| id | emp_name | dept  | salary  |
+----+----------+-------+---------+
|  1 | Sam      | Engg  | 1000.00 |
|  2 | Smith    | Engg  | 2000.00 |
|  3 | Denis    | HR    | 1500.00 |
|  4 | Danny    | HR    | 3000.00 |
|  5 | David    | IT    | 2000.00 |
|  6 | John     | IT    | 3000.00 |
|  7 | Ravi     | Sales | 4000.00 |
|  8 | Bob      | Sales | 6000.00 |
|  9 | Rick     | HR    | 4500.00 |
| 10 | Mathew   | IT    | 2500.00 |
+----+----------+-------+---------+

问题:

查找每个部门中薪水最高的员工

输出:

+----+----------+-------+---------+
| id | emp_name | dept  | salary  |
+----+----------+-------+---------+
|  8 | Bob      | Sales | 6000.00 |
|  9 | Rick     | HR    | 4500.00 |
|  6 | John     | IT    | 3000.00 |
|  2 | Smith    | Engg  | 2000.00 |
+----+----------+-------+---------+

当我尝试时，我遇到了很多错误。为什么会出现错误？

SELECT id, emp_name, salary, dept 
FROM employees 
GROUP BY dept;

预期结果:

+----+----------+-------+---------+
| id | emp_name | dept  | salary  |
+----+----------+-------+---------+
|  8 | Bob      | Sales | 6000.00 |
|  9 | Rick     | HR    | 4500.00 |
|  6 | John     | IT    | 3000.00 |
|  2 | Smith    | Engg  | 2000.00 |
+----+----------+-------+---------+

实际结果:

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test_db.employees.ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Answer 1

您应该使用以下查询:

SELECT A.*
FROM EMPLOYEES A
JOIN (
  SELECT DEPT, MAX(SALARY) SALARY
  FROM EMPLOYEES
  GROUP BY DEPT
) B ON A.SALARY = B.SALARY AND A.DEPT = B.DEPT