背景说明

工作过程中，经常遇到一个需求：根据某一字段进行分组，取每个分组中的最大值的一行，把每个分组最大值行形成一个行集。

基础数据

假设有一个user表，表数据如下

id	group_name	score	name
1	a	20	a1
2	a	26	a2
3	a	80	a3
4	a	30	a4
5	b	10	b1
6	b	30	b2
7	b	90	b3
8	b	20	b4

目标数据如下：

id	group_name	score	name
3	a	80	a3
7	b	90	b3

基础数据脚本

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `score` int(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'a', 20, 'a1');
INSERT INTO `user` VALUES (2, 'a', 26, 'a2');
INSERT INTO `user` VALUES (3, 'a', 80, 'a3');
INSERT INTO `user` VALUES (4, 'a', 30, 'a4');
INSERT INTO `user` VALUES (5, 'b', 10, 'b1');
INSERT INTO `user` VALUES (6, 'b', 30, 'b2');
INSERT INTO `user` VALUES (7, 'b', 90, 'b3');
INSERT INTO `user` VALUES (8, 'b', 20, 'b4');

SET FOREIGN_KEY_CHECKS = 1;

思路分析

首先运行SQL,得到组字段和最大值字段

SELECT
    group_name,
    MAX( score )  as max_scroe
FROM
    `user` 
GROUP BY
    group_name

得到结果集如下

group_name	max_scroe
a	80
b	90

此时只需要和原始表进行关联，把group_name和max_scroe作为条件相等即可

实现方案

按照实现思路编写SQL如下

SELECT
    u.* 
FROM
    `user` AS u
    INNER JOIN ( 
                SELECT group_name, MAX( score ) AS max_scroe 
                FROM `user` 
                GROUP BY group_name ) AS u_group_done
    ON u.group_name = u_group_done.group_name  AND u.score = u_group_done.max_scroe

执行结果如下

id	group_name	score	name
3	a	80	a3
7	b	90	b3

如果业务逻辑复杂且存在计算属性，例如使用了CAST函数，假设score为字符类型则多包一层子查询即可

SELECT
    u.* 
FROM
    `user` AS u
    INNER JOIN (
        SELECT group_name,MAX(max_int) as max_score
        FROM(
                SELECT
                    group_name,
                    CAST( score AS SIGNED ) AS max_int
                FROM
                    `user` ) as cast_done_user
                GROUP BY group_name
     ) as cast_done_user_group_done 
     on u.group_name=cast_done_user_group_done.group_name AND u.score=cast_done_user_group_done.max_score

得到结果集合