Mysql分组查询时间最近的一条数据
需求描述
现有图书馆借书记录表如下：需要分组查询每个学生最近一次借书的记录
解决方案
-
1.方法1，嵌套子查询
select *from
borrow_books_record br,
(select student_id , max(borrow_time) as lastBorrowTime from borrow_books_record
group by student_id) br1
where
br.student_id=br1.student_id and br.borrow_time = br1.lastBorrowTime
group by br.student_id
执行这段sql语句，查询到的就是每个学生最近一次借书的数据。
-
方法2 内连接+聚合函数
select
br.student_id,
br.student_name,
br.book_name,
br.borrow_time,
br.predict_return_date,
br.actual_return_time
from
borrow_books_record br
inner join(
select student_id,max(borrow_time) as lastBorrowTime
from borrow_books_record
group by student_id
) br1 on br1.student_id=br.student_id and br1.lastBorrowTime = br.borrow_time
