用于查找每本书的最后一次发行/退货记录状态的SQL查询

Question

Books表

​

​

图书出退货记录表

​

​

我需要一个SQL查询来查找每本书的最后一个问题/退货记录的状态

这就是我靠自己做到的：

SELECT 
    `Ref_No`, 
     IF(`book_issue_chi`.`Status`='Loaned', 'No', 'Yes') as 'Available on loan' 
FROM `books_chi` 
LEFT JOIN `book_issue_chi` ON `book_issue_chi`.`Book_Number`=`books_chi`.`ID` 

如果图书只有一条问题/退货记录，则返回正确，但如果图书上有多条问题/退货记录，则返回错误。任何帮助或提示都将受到欢迎。

books_chi表

​

book_issue_chi表

​

mysql查询结果

​

如何修改可以返回每本书最后一条出库/退货记录状态的SQL查询？请帮帮我！谢谢。

Answer 1

在子查询中查找每本书的最大发行日期：

select *
from books_chi b
left join book_issue_chi bi
  on bi.book_number = b.id
  and (bi.book_number, bi.issue_date) in
  (
    select book_number, max(issue_date)
    from book_issue_chi
    group by book_number
  );

替代方法:从MySQL 8开始，您可以使用窗口函数(MAX OVER)来获取每本书的最大发行日期。因此，您只需阅读问题表一次。

select *
from books_chi b
left join
(
  select
    book_issue_chi.*,
    max(issue_date) over(partition by book_number) as max_issue_date
  from book_issue_chi
) bi on bi.book_number = b.id
     and bi.issue_date = bi.max_issue_date;