在日常开发中，经常会遇到查询分组数据中的最新记录，比如统计当前系统中每个人的最新登录记录，外卖系统统计所有买家的最新订单记录，图书馆管理系统借书人最近借的书。etc今天给大家介绍一下上面场景下SQL的写法，希望对大家有所帮助！1.初始化数据表--借款人表CREATETABLE`userinfo`(`uid`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主键',`varchar(20)NOTNULLCOMMENT'name',`uage`int(11)NOTNULLCOMMENT'age',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','Reader');--借阅记录表CREATETABLE`borrow_record`(`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主键',`user_id`int(11)NOTNULLCOMMENT'userid',`book_id`int(11)NOTNULLCOMMENT'bookid',`borrowtime`datetimeNOTNULLCOMMENT'bookid',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'Writing1directgroupbyAccordingtouserid,使用聚合函数max获取最近浏览时间selecta.user_id,max(c.u名称）uname，max（a.borrowtime）borrowtime，max（b.book_name）book_namefromborrow_recordaINNERJOINbookinfobonb.id=a.book_idINNERJOINuserinfoconc.uid=a.user_idGROUPBYa.user_id--解释：这样一来，书名会出现混淆，--因为使用聚合函数得到的书名不是必然是对应的用户--最新的浏览记录对应的书名写法2使用子查询的方法获取借阅记录表的最新浏览时间作为查询条件selecta.user_id,c.uname,a.borrowtime,b.book_namebook_namefromborrow_recordaINNERJOINbookinfobonb.id=a.book_idINNERJOINuserinfoconc.uid=a.user_idwherea.borrowtime=(selectmax(borrowtime)fromborrow_recordtwhereet.user_id=a.user_id)--说明：可以满足查询效果，但是性能不是最优，a。borrowtime，b.book_namebook_namefrom（selectt.user_id，max（borrowtime）borrowtimefromborrow_recordtGROUPBYt.user_id）aseINNERJOINborrow_recordaone.user_id=a.user_idande.borrowtime=a.borrowtimeINNERJOINbookinfobonb.id=a.book_idinfaconJOIN效果如下：
