需要 mySQL 子选择

Question

我有三张表:图书馆元素、副本和借阅。

一个图书馆项目有许多个副本，以及一个有许多个借阅副本。

我正在尝试获取最新贷款条目的副本；下面的查询返回给定副本的所有外借。

SELECT 
libraryitems.title,
copies.id, 
copies.qruuid,
loans.id AS loanid, 
loans.status, 
loans.byname, 
loans.byemail, 
loans.createdAt 
FROM copies 
INNER JOIN libraryitems ON copies.libraryitemid = libraryitems.id AND libraryitems.deletedAt IS NULL 
LEFT OUTER JOIN loans ON copies.id = loans.copyid 
WHERE copies.libraryitemid = 1 
ORDER BY copies.id ASC, loans.createdAt DESC 

我知道这里需要对某些描述进行子选择，但很难获得正确的语法。如何仅返回每个不同副本的最新行(即 MAX(loans.createdAt) 行)？仅使用 group bycopys.id 返回最早的条目，而不是最新的条目。

下面的图像示例:

Answer 1

在子查询中，获取贷款的最大创建时间，即最新条目并与贷款连接以获取其他详细信息。

SELECT 
T.title,
T.id, 
T.qruuid,
loans.id AS loanid, 
loans.status, 
loans.byname, 
loans.byemail, 
loans.createdAt 
FROM
(
   SELECT C.id, C.qruuid, L.title, MAX(LN.createdAt) as maxCreatedTime
   FROM Copies C
   INNER JOIN libraryitems L ON C.libraryitemid = L.id 
   AND L.deletedAt IS NULL
   LEFT OUTER JOIN loans LN ON C.id = LN.copyid
   GROUP BY C.id, C.qruuid, L.title) T
 JOIN loans ON T.id = loans.copyid 
AND T.maxCreatedTime = loans.createdAt