mysql - MySQL中按聚合函数max分组

Question

我的表讲师如下:

+-------+------------+------------+----------+
| ID    | name       | dept_name  | salary   |
+-------+------------+------------+----------+
| 10101 | Srinivasan | Comp. Sci. | 65000.00 |
| 12121 | Wu         | Finance    | 90000.00 |
| 15151 | Mozart     | Music      | 40000.00 |
| 22222 | Einstein   | Physics    | 95000.00 |
| 32343 | El Said    | History    | 60000.00 |
| 33456 | Gold       | Physics    | 87000.00 |
| 45565 | Katz       | Comp. Sci. | 75000.00 |
| 58583 | Califieri  | History    | 62000.00 |
| 76543 | Singh      | Finance    | 80000.00 |
| 76766 | Crick      | Biology    | 72000.00 |
| 83821 | Brandt     | Comp. Sci. | 92000.00 |
| 98345 | Kim        | Elec. Eng. | 80000.00 |
+-------+------------+------------+----------+

查询

 select dept_name, max(salary) from instructor group by dept_name;

会给我导师每个部门的最高工资。

但是，我想获取结果中的所有列，即每个部门中收入最高的讲师的ID和姓名。但我不知道该怎么做。

Answer 1

您可以使用当前查询作为过滤原始表的子查询:

SELECT i1.*
FROM instructor i1
INNER JOIN
(
    SELECT dept_name, MAX(salary) AS salary
    FROM instructor
    GROUP BY dept_name
) i2
    ON i1.dept_name = i2.dept_name AND i1.salary = i2.salary;