日常开发当中，经常会遇到查询分组数据中最新的一条记录，比如统计当前系统每个人的最新登录记录、外卖系统统计所有买家最新的一次订单记录、图书管理系统借阅者最新借阅书籍的记录等等。今天给大家介绍一下如何实现以上场景的SQL写法，希望对大家能有所帮助!

1、初始化数据表

--借阅者表
CREATETABLE`userinfo`(
`uid`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主键',
`uname`varchar(20)NOTNULLCOMMENT'姓名',
`uage`int(11)NOTNULLCOMMENT'年龄',
PRIMARYKEY(`uid`)USINGBTREE
)ENGINE=InnoDBAUTO_INCREMENT=8DEFAULTCHARSET=utf8ROW_FORMAT=COMPACT;
INSERTINTO`userinfo`VALUES(1,'小明',20);
INSERTINTO`userinfo`VALUES(2,'小张',30);
INSERTINTO`userinfo`VALUES(3,'小李',28);
--书籍表
CREATETABLE`bookinfo`(
`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主键',
`book_no`varchar(20)NOTNULLCOMMENT'书籍编号',
`book_name`varchar(20)NOTNULLCOMMENT'书籍名称',
PRIMARYKEY(`id`)USINGBTREE
)ENGINE=InnoDBAUTO_INCREMENT=14DEFAULTCHARSET=utf8ROW_FORMAT=COMPACT;
INSERTINTO`bookinfo`VALUES(1,'ISBN001','计算机基础');
INSERTINTO`bookinfo`VALUES(2,'ISBN002','计算机网络');
INSERTINTO`bookinfo`VALUES(3,'ISBN003','高等数学');
INSERTINTO`bookinfo`VALUES(4,'ISBN004','明朝那些事');
INSERTINTO`bookinfo`VALUES(5,'ISBN005','物理');
INSERTINTO`bookinfo`VALUES(13,'ISBN006','读者');
--借阅记录表
CREATETABLE`borrow_record`(
`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主键',
`user_id`int(11)NOTNULLCOMMENT'用户id',
`book_id`int(11)NOTNULLCOMMENT'书籍id',
`borrowtime`datetimeNOTNULLCOMMENT'书籍id',
PRIMARYKEY(`id`)USINGBTREE
)ENGINE=InnoDBAUTO_INCREMENT=16DEFAULTCHARSET=utf8ROW_FORMAT=COMPACT;
INSERTINTO`borrow_record`VALUES(8,1,2,'2021-05-0110:52:00');
INSERTINTO`borrow_record`VALUES(9,2,4,'2021-07-1223:32:00');
INSERTINTO`borrow_record`VALUES(10,2,1,'2021-03-2109:00:00');
INSERTINTO`borrow_record`VALUES(11,1,3,'2021-08-1117:39:00');
INSERTINTO`borrow_record`VALUES(12,1,5,'2021-09-0218:12:00');
INSERTINTO`borrow_record`VALUES(13,3,1,'2021-07-0612:32:00');
INSERTINTO`borrow_record`VALUES(14,2,1,'2021-08-0910:10:00');
INSERTINTO`borrow_record`VALUES(15,4,3,'2021-04-1519:45:00'

写法1 直接group by 根据userid ，使用聚合函数max取得最近的浏览时间

selecta.user_id,max(c.uname)uname
,max(a.borrowtime)borrowtime,max(b.book_name)book_name
fromborrow_recorda
INNERJOINbookinfobonb.id=a.book_id
INNERJOINuserinfoconc.uid=a.user_id
GROUPBYa.user_id
--说明：这样会存在获取书籍名称错乱的情况，
--因为使用聚合函数获取的书籍名称，不一定是对应用户
--最新浏览记录对应的书籍名称

写法2 采用子查询的方式，获取借阅记录表最近的浏览时间作为查询条件

selecta.user_id,c.uname,a.borrowtime
,b.book_namebook_namefromborrow_recorda
INNERJOINbookinfobonb.id=a.book_id
INNERJOINuserinfoconc.uid=a.user_id
wherea.borrowtime=(selectmax(borrowtime)
fromborrow_recordtwheret.user_id=a.user_id)
--说明：可以满足查询效果，不过性能不是最优解

写法3 采用group by + join 性能最高，推荐采用

selecta.user_id,c.uname,a.borrowtime
,b.book_namebook_namefrom(
selectt.user_id,max(borrowtime)borrowtime
fromborrow_recordtGROUPBYt.user_id)ase

INNERJOINborrow_recordaone.user_id=a.user_id
ande.borrowtime=a.borrowtimeINNER
JOINbookinfobonb.id=a.book_id
INNERJOINuserinfoconc.uid=a.user_id

运行效果如下：