mysql 分组获取最新一条记录

1. 需求

按用户名分组，获取最新插入的一条记录。

2. 模拟数据

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'jack', '2020-03-21 16:55:07');
INSERT INTO `user` VALUES ('2', 'jack', '2020-03-21 16:55:18');
INSERT INTO `user` VALUES ('3', 'jack', '2020-03-21 16:55:25');
INSERT INTO `user` VALUES ('4', 'marry', '2020-03-21 16:55:33');
INSERT INTO `user` VALUES ('5', 'marry', '2020-03-21 16:55:37');

需要获取id为 3 和 5 的数据。

3. 方法1

注意limit或distinct是必须的。

select * from (select * from user order by create_time desc limit 10000000) as u GROUP BY u.username;
select * from (select DISTINCT * from user order by create_time desc) as u GROUP BY u.username;

4. 方法2

4.1 实现方法

SELECT u1.*,u2.*
FROM `user` u1 LEFT JOIN `user` u2
ON (u1.username = u2.username AND u1.create_time < u2.create_time) 
WHERE u2.id is NULL;

4.2 方法分析

（1）首先来看，不加u1.create_time < u2.create_time和where条件，查询结果为笛卡尔积；
（2）然后，加上u1.create_time < u2.create_time条件后，就会过滤一部分数据，剩下的数据特征是：左侧<右侧，左侧最大的数据，右侧用null填充。此时数据为：

（3）最后，右侧为null的即为create_time最大（即最新插入的数据），所以加上WHERE u2.id is NULL后查询结果为：