MYSQL 获取分组某字段最大的记录

发表于   |   分类于

考虑这个问题源自 LeetCode 上的一道题：184. Department Highest Salary

大概意思就是用下面两张表(Employee、Department)合成最后的一张表，得到以 DepartmentId 分组的 Salary 值最大的记录：

Employee

1
2
3
4
5
6
7
8
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

Department

1
2
3
4
5
6
+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

最终得到：

1
2
3
4
5
6
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

思路其实很清晰：

  1. 「找最大记录」Employee 表中找到以 DepartmentId 分组的 Salary 值最大的记录
  2. 「连接」Employee 表与 Department 表连接

或

  1. 「连接」 Employee 表与 Department 表连接
  2. 「找最大记录」连接后的表中找到以 DepartmentId 分组的 Salary 值最大的记录

那么最大的问题就停留在：如何找到按字段 1 分组的字段 2 最大的记录？

先后尝试了以下几种方法：

方法一（不可行）：

1
select * from (select * from table2 order by age desc) as a group by a.table1_id

方法二：

1
select a.* from table2 as a where age = (select max(age) from table2 where a.table1_id=table1_id)

方法三：

1
select a.* from table2 as a where not exists (select * from table2 where table1_id=a.table1_id and age>a.age)

方法四：

1
select a.* from table2 as a where exists (select count(*) from table2 where table1_id=a.table1_id and age>a.age having count(*)=0)

第一种方法不可行的原因是由于违反了 ONLY_FULL_GROUP_BY 模式要求，具体原因参见 SQL_MODE = ONLY_FULL_GROUP_BY。方法二三四亲测都是可行的，那么使用思路一，先找到 Employee 表中以 DepartmentId 分组的 Salary 值最大的记录，再连接的写法如下：

1
2
3
4
5
6
# 方法一
select b.Name as Department, c.name as Employee, Salary from (
  # 单表中找出分组最大
  select a.* from Employee as a where salary = (select max(salary) from Employee where a.DepartmentId = DepartmentId) 
) c
INNER JOIN Department AS b ON c.DepartmentId = b.id
1
2
3
4
5
6
# 方法二
select b.Name as Department, c.name as Employee, Salary from (
  # 单表中找出分组最大
  select a.* from Employee as a where not exists (select * from Employee where DepartmentId=a.DepartmentId and Salary > a.Salary)
) c
INNER JOIN Department AS b ON c.DepartmentId = b.id
1
2
3
4
5
6
# 方法三
select b.Name as Department, c.name as Employee, Salary from (
  # 单表中找出分组最大
  select a.* from Employee as a where exists (select count(*) from Employee where DepartmentId=a.DepartmentId and Salary > a.Salary having count(*)=0)
) c
INNER JOIN Department AS b ON c.DepartmentId = b.id