与GROUP BY子句一起使用的MAX函数 [英] MAX function used with GROUP BY clause

问题描述

我有一个包含姓名，工资和员工部门的数据库。
我需要一个查询来获取每个部门中薪水最高的员工。

I have database with name, salary and department of employees. I need a query for getting employee(s) with highest salaries in each department.

数据库：

create table test(
    employee_name VARCHAR(255),
    department VARCHAR(255),
    salary INT  
);

数据：

INSERT INTO test(employee_name, department, salary) VALUES 
("John", "DepartmentA", 1500),
("Sarah","DepartmentA", 1600),
("Romel","DepartmentA", 1400),
("Victoria","DepartmentB", 1400),
("Maria",   "DepartmentB", 1600);

我的尝试：

1.1最大值（薪水）=部门薪金

1.1 WHERE MAX(salary) = salary GROUP BY department

SELECT employee_name, salary FROM test WHERE MAX(salary) = salary GROUP BY department;
ERROR 1111 (HY000): Invalid use of group function

1.2。当我将MAX（salary）替换为硬编码值时，它按我的预期工作：

1.2. when I replace MAX(salary) with hardcoded value, it works as I expect:

SELECT employee_name, salary FROM test WHERE 1600 = salary GROUP BY department;
+---------------+--------+
| employee_name | salary |
+---------------+--------+
| Sarah         |   1600 |
| Maria         |   1600 |
+---------------+--------+
2 rows in set (0.00 sec)

1. 有子句的错误答案（单个结果，不是每个部门）：

1. Wrong answer with having clause (single result, not per department):

选择employee_name，薪水来自部门的测试GROUP HAVING MAX（salary）=薪水；

SELECT employee_name, salary FROM test GROUP BY department HAVING MAX(salary) = salary;

+ --------------- + -------- +
|员工名|薪水|
+ --------------- + -------- +
|玛丽亚| 1600 |
+ --------------- + -------- +
集合中的1行（0.00秒）

+---------------+--------+ | employee_name | salary | +---------------+--------+ | Maria | 1600 | +---------------+--------+ 1 row in set (0.00 sec)

我期望得到的结果：

Sarah, DepartmentA
Maria, DepartmentB

推荐答案

首先必须获得每个部门的最高薪资：

First you have to get the maximum salary for each department:

SELECT department, max(salary) as max_salary
FROM test
GROUP BY department

然后您可以将此子查询重新加入测试表：

then you can join back this subquery to the test table:

SELECT t.*
FROM
  test t INNER JOIN (
    SELECT department, max(salary) as max_salary
    FROM test
    GROUP BY department
  ) d ON t.department=d.department AND t.salary=d.max_salary

这篇关于与GROUP BY子句一起使用的MAX函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！