SQL查询：求每个组最大值，原来是这样写的（SQL分组求最值有哪些坑）

背景介绍：

这是我9月份的一道SQL面试题，当时我答错了，于是写了这篇博客总结一下

表和数据如下

CREATE TABLE `user` (
  `uid` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `dept_id` int(11) DEFAULT NULL COMMENT '分组id',
  `name` varchar(255) DEFAULT NULL COMMENT '用户名',
  `score` int(11) DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '1', 'a', '12');
INSERT INTO `user` VALUES ('2', '1', 'aa', '13');
INSERT INTO `user` VALUES ('3', '1', 'aaa', '99');
INSERT INTO `user` VALUES ('4', '2', 'bb', '40');
INSERT INTO `user` VALUES ('5', '3', 'c', '60');
INSERT INTO `user` VALUES ('6', '2', 'b', '12');
INSERT INTO `user` VALUES ('7', '2', 'bbb', '1');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

需求

按照dept_id进行分组，求每个分组的score的最大值

我的答案

SELECT * FROM `user` GROUP BY dept_id HAVING score = MAX(score)
1

运行结果

mysql> SELECT * FROM `user` GROUP BY dept_id HAVING score = MAX(score);
+-----+--------+-------+-------+
| uid | dept_id | name | score |
+-----+--------+-------+-------+
|   4 |      2 | bb    |    40 |
|   5 |      3 | c     |    60 |
+-----+--------+-------+-------+
2 rows in set
1
2
3
4
5
6
7
8

SQL语句执行顺序

1.	FROM <表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。
2.  (left/right) JOIN  #指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
3.  ON	<筛选条件> # 对笛卡尔积的虚表进行筛选
4.  WHERE <筛选条件>  # 对上述虚表进行筛选
5.  GROUP BY <分组条件> # 分组
6.  HAVING  <分组筛选> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
7.  SELECT  #返回数据列表
8.  DISTINCT （或者其它聚合函数）
9.  ORDER BY 排序
1
2
3
4
5
6
7
8
9

问题分析

先看一下直接查询表中全部数据

mysql> SELECT * FROM `user`;
+-----+--------+-------+-------+
| uid | dept_id | name | score |
+-----+--------+-------+-------+
|   1 |      1 | a     |    12 |
|   2 |      1 | aa    |    13 |
|   3 |      1 | aaa   |    99 |
|   4 |      2 | bb    |    40 |
|   5 |      3 | c     |    60 |
|   6 |      2 | b     |    12 |
|   7 |      2 | bbb   |     1 |
+-----+--------+-------+-------+
7 rows in set
1
2
3
4
5
6
7
8
9
10
11
12
13

再看一下分组查询的结果

mysql> SELECT * FROM `user` GROUP BY dept_id;
+-----+--------+-------+-------+
| uid | dept_id | name | score |
+-----+--------+-------+-------+
|   1 |      1 | a     |    12 |
|   4 |      2 | bb    |    40 |
|   5 |      3 | c     |    60 |
+-----+--------+-------+-------+
3 rows in set
1
2
3
4
5
6
7
8
9

分组后加上Having MAX

mysql> SELECT * FROM `user` GROUP BY dept_id HAVING score = MAX(score);
+-----+--------+-------+-------+
| uid | dept_id | name | score |
+-----+--------+-------+-------+
|   4 |      2 | bb    |    40 |
|   5 |      3 | c     |    60 |
+-----+--------+-------+-------+
2 rows in set
1
2
3
4
5
6
7
8

最终分析

每个分组的最大值分别为：

• 1：90
• 2：40
• 3：60

由于直接运行SELECT * FROM user GROUP BY dept_id返回的结果中，分组为2，3的数据中，score是2，3组的最大值，加上Having max过滤后任然没有被过滤掉，而分组1返回的数据中，score不是最大值，被过滤掉了

另一个看起来正确但实际上是错误的答案

mysql> SELECT uid,dept_id,`name`,MAX(score)  FROM `user` GROUP BY dept_id;
+-----+--------+-------+------------+
| uid | dept_id | name | MAX(score) |
+-----+--------+-------+------------+
|   1 |      1 | a     |         99 |
|   4 |      2 | bb    |         40 |
|   5 |      3 | c     |         60 |
+-----+--------+-------+------------+
3 rows in set
1
2
3
4
5
6
7
8
9

不仔细看是不是以为答案正确了，但是，有没有发现uid和score对不上啊

正确答案

SQL语句

SELECT *  FROM `user` as u1 WHERE u1.score =(
	SELECT MAX(score) FROM `user` as u2 WHERE u1.dept_id=u2.dept_id);
1
2

运行结果

mysql> SELECT *  FROM `user` as u1 WHERE u1.score =(
	SELECT MAX(score) FROM `user` as u2 WHERE u1.dept_id=u2.dept_id);
+-----+--------+-------+-------+
| uid | dept_id | name | score |
+-----+--------+-------+-------+
|   3 |      1 | aaa   |    99 |
|   4 |      2 | bb    |    40 |
|   5 |      3 | c     |    60 |
+-----+--------+-------+-------+
3 rows in set
1
2
3
4
5
6
7
8
9
10

最终的答案参考了这篇博客，地址如下：https://www.cnblogs.com/lakeliu/p/11943946.html