mysql 分组获取最新一条记录

叁歲伎倆 2023-07-17 12:50 116阅读 0赞

#### 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. 实现方法 ####

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. 方法分析 ####

（1）首先来看，不加`u1.create_time < u2.create_time`和where条件，查询结果为笛卡尔积；  
（2）然后，加上`u1.create_time < u2.create_time`条件后，就会过滤一部分数据，**剩下的数据特征是：左侧<右侧，左侧最大的数据，右侧用null填充**。此时数据为：  
![在这里插入图片描述][20200321174053594.png]  
（3）最后，右侧为null的即为create\_time最大（即最新插入的数据），所以加上`WHERE u2.id is NULL`后查询结果为：  
![在这里插入图片描述][20200321174200406.png]

[20200321174053594.png]: /images/20230528/fa779eb657114542a45293efe9d1ba2f.png
[20200321174200406.png]: /images/20230528/df9220ba3f9f489ea254a7e0b833747c.png