创建测试表及其数据
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