数据表
测试sql
CREATE TABLE `test11` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`in_date` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of test11
-- ----------------------------
INSERT INTO `test11` VALUES ('2', '张三2', '2', '2022-08-20 16:43:21');
INSERT INTO `test11` VALUES ('3', '张三3', '3', '2022-08-20 16:43:22');
INSERT INTO `test11` VALUES ('4', '张三4', '1', '2022-08-20 16:44:24');
INSERT INTO `test11` VALUES ('5', '张三5', '2', '2022-08-20 16:44:25');
INSERT INTO `test11` VALUES ('6', '张三6', '3', '2022-08-20 16:44:28');
INSERT INTO `test11` VALUES ('7', '张三7', '1', '2022-08-20 16:44:30');
INSERT INTO `test11` VALUES ('8', '张三8', '2', '2022-08-20 16:44:38');
INSERT INTO `test11` VALUES ('9', '张三9', '3', '2022-08-20 16:44:40');
INSERT INTO `test11` VALUES ('10', '张三10', '1', '2022-08-20 16:44:44');
INSERT INTO `test11` VALUES ('11', '张三11', '2', '2022-08-20 16:44:46');
INSERT INTO `test11` VALUES ('1', '张三1', '1', '2022-08-20 16:43:18');
INSERT INTO `test11` VALUES ('12', '张三12', '3', '2022-08-20 16:44:47');
我们要获取的每类型组最新的数据应该是10,11,12这三条.
方法1:
SELECT * FROM(SELECT * from test11 ORDER BY in_date DESC LIMIT 3) a GROUP BY type
方法2:
SELECT * FROM(SELECT DISTINCT * from test11 ORDER BY in_date DESC) a GROUP BY type