当我们要实现SQL分组后取第一条数据则需要进行排序结果作为子查询后分组
CREATE TABLE `op_joke` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name1` varchar(255) DEFAULT NULL,
`name2` varchar(255) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_push` (`name1`,`name2`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
INSERT INTO `test_yjdsns`.`op_joke` (`id`, `name1`, `name2`, `create_time`) VALUES (9, '我', NULL, '2022-10-01 15:14:22');
INSERT INTO `test_yjdsns`.`op_joke` (`id`, `name1`, `name2`, `create_time`) VALUES (10, '我', NULL, '2022-11-01 15:14:22');
INSERT INTO `test_yjdsns`.`op_joke` (`id`, `name1`, `name2`, `create_time`) VALUES (11, '你', NULL, NULL);
INSERT INTO `test_yjdsns`.`op_joke` (`id`, `name1`, `name2`, `create_time`) VALUES (12, '你', NULL, NULL);
语句中加个distinct
就好了
SELECT
t1.*
FROM
( SELECT DISTINCT * FROM op_joke ORDER BY create_time DESC ) t1
GROUP BY
t1.name1
ORDER BY
t1.id;
究其原因应该是和mysql5.7添加的derived_merge 有关系