mysql 分组查询每组值最大的记录_mysql分组查询每组信息的最大值及所有信息

创建测试表及其数据

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for type

-- ----------------------------

DROP TABLE IF EXISTS `type`;

CREATE TABLE `type`  (

`id` int(11) NOT NULL,

`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,

`type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,

`age` int(11) DEFAULT NULL,

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of type

-- ----------------------------

INSERT INTO `type` VALUES (1, ‘a‘, ‘1‘, 19);

INSERT INTO `type` VALUES (2, ‘b‘, ‘1‘, 10);

INSERT INTO `type` VALUES (3, ‘c‘, ‘2‘, 20);

INSERT INTO `type` VALUES (4, ‘d‘, ‘2‘, 30);

SET FOREIGN_KEY_CHECKS = 1;

查询sql (分析  (select type,max(age) age  from type  GROUP BY type) as ls 先分组查看每组最大的age  (但是name字段无法查看))

然后在作为一个临时表 关联type表 加个age关联字段 确保查看的信息是最大的一条

select  t.* from type  t   join (select type,max(age) age  from type  GROUP BY type) as ls on ls.age=t.age

觉得有用的话 麻烦随手点赞一下 创作不易 您的点赞 将是我最大的动力 十分感谢！

原文：https://www.cnblogs.com/atsong/p/13092197.html