在MySQL中如何使用GROUP BY获取最新记录的三种方法

在MySQL中如何使用GROUP BY获取最新记录的三种方法

在日常开发中，经常会遇到需要查询分组数据中最新的一条记录的场景，比如统计当前系统每个人的最新登录记录、外卖系统统计所有买家最新的一次订单记录、图书管理系统借阅者最新借阅书籍的记录等等。本文将通过一个具体的案例，介绍如何在MySQL中使用GROUP BY获取最新记录的三种不同方法，并对每种方法的优劣进行分析。

初始化数据表

首先，我们创建三个表：借阅者表（userinfo）、书籍表（bookinfo）和借阅记录表（borrow_record），并插入一些示例数据。

-- 借阅者表 
CREATE TABLE `userinfo` ( 
 `uid` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', 
 `uname` varchar(20) NOT NULL COMMENT '姓名', 
 `uage` int(11) NOT NULL COMMENT '年龄', 
 PRIMARY KEY (`uid`) USING BTREE 
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; 
INSERT INTO `userinfo` VALUES (1, '小明', 20); 
INSERT INTO `userinfo` VALUES (2, '小张', 30); 
INSERT INTO `userinfo` VALUES (3, '小李', 28); 

-- 书籍表 
CREATE TABLE `bookinfo` ( 
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', 
 `book_no` varchar(20) NOT NULL COMMENT '书籍编号', 
 `book_name` varchar(20) NOT NULL COMMENT '书籍名称', 
 PRIMARY KEY (`id`) USING BTREE 
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; 
INSERT INTO `bookinfo` VALUES (1, 'ISBN001', '计算机基础'); 
INSERT INTO `bookinfo` VALUES (2, 'ISBN002', '计算机网络'); 
INSERT INTO `bookinfo` VALUES (3, 'ISBN003', '高等数学'); 
INSERT INTO `bookinfo` VALUES (4, 'ISBN004', '明朝那些事'); 
INSERT INTO `bookinfo` VALUES (5, 'ISBN005', '物理'); 
INSERT INTO `bookinfo` VALUES (13, 'ISBN006', '读者'); 

-- 借阅记录表 
CREATE TABLE `borrow_record` ( 
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', 
 `user_id` int(11) NOT NULL COMMENT '用户id', 
 `book_id` int(11) NOT NULL COMMENT '书籍id', 
 `borrowtime` datetime NOT NULL COMMENT '书籍id', 
 PRIMARY KEY (`id`) USING BTREE 
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; 
INSERT INTO `borrow_record` VALUES (8, 1, 2, '2021-05-01 10:52:00'); 
INSERT INTO `borrow_record` VALUES (9, 2, 4, '2021-07-12 23:32:00'); 
INSERT INTO `borrow_record` VALUES (10, 2, 1, '2021-03-21 09:00:00'); 
INSERT INTO `borrow_record` VALUES (11, 1, 3, '2021-08-11 17:39:00'); 
INSERT INTO `borrow_record` VALUES (12, 1, 5, '2021-09-02 18:12:00'); 
INSERT INTO `borrow_record` VALUES (13, 3, 1, '2021-07-06 12:32:00'); 
INSERT INTO `borrow_record` VALUES (14, 2, 1, '2021-08-09 10:10:00'); 
INSERT INTO `borrow_record` VALUES (15, 4, 3, '2021-04-15 19:45:00');

方法1：直接使用GROUP BY和聚合函数

select a.user_id ,max(c.uname) uname 
,max(a.borrowtime) borrowtime,max(b.book_name) book_name 
from borrow_record a 
INNER JOIN bookinfo b on b.id=a.book_id 
INNER JOIN userinfo c on c.uid=a.user_id 
GROUP BY a.user_id 
-- 说明： 这样会存在获取书籍名称错乱的情况， 
-- 因为使用聚合函数获取的书籍名称，不一定是对应用户 
-- 最新浏览记录对应的书籍名称 

这种方法虽然简单，但存在一个问题：使用聚合函数获取的书籍名称可能不是用户最新浏览记录对应的书籍名称。

方法2：使用子查询

select a.user_id ,c.uname,a.borrowtime 
,b.book_name book_namefrom borrow_record a 
INNER JOIN bookinfo b on b.id=a.book_id 
INNER JOIN userinfo c on c.uid=a.user_id 
where a.borrowtime=(select max(borrowtime) 
from borrow_record t where t.user_id=a.user_id) 
-- 说明：可以满足查询效果，不过性能不是最优解 

这种方法可以得到正确的结果，但性能可能不是最优的，因为子查询在大数据量下可能会导致性能问题。

方法3：使用GROUP BY + JOIN

select a.user_id ,c.uname,a.borrowtime 
,b.book_name book_namefrom ( 
select t.user_id,max(borrowtime) borrowtime 
from borrow_record t GROUP BY t.user_id) as e 
INNER JOIN borrow_record a on e.user_id=a.user_id 
and e.borrowtime=a.borrowtimeINNER 
JOIN bookinfo b on b.id=a.book_id 
INNER JOIN userinfo c on c.uid=a.user_id 

这种方法是推荐使用的，因为它既保证了查询的正确性，又优化了性能。通过先获取每个用户的最新借阅时间，再与借阅记录表进行连接，可以避免使用子查询带来的性能问题。

运行效果

通过以上三种方法的对比，可以看出第三种方法在实际应用中最为推荐。希望本文能帮助读者更好地理解和掌握MySQL中GROUP BY的使用技巧。