mysql - 子查询返回多个值，=、!=、<、<=、>、>=

Question

在查询中实现子查询，使用 max(dateOut) 返回 DVD 的最后一次外借删除。

该查询在贷款表中给出了正确的返回，但是当尝试作为子查询实现时出现错误，知道应该做什么吗？ max(dateout) 部分。测试时报错如下图。

SELECT 
    DVDTitle.DVDTitle, 
    Loan.LoanNumber, 
    Member.MemberNumber, 
    Member.MemberFirstName, 
    Member.MemberLastName, 
    (SELECT max(DateOut) FROM Loan, DVDCopy GROUP BY DVDCopy.CopyNumber), 
    Loan.DateDue, Loan.DateReturned
FROM 
    DVDCopy 
INNER JOIN 
    Loan 
    ON DVDCopy.CopyNumber = Loan.CopyNumber 
INNER JOIN 
    Member 
    ON Loan.MemberNumber = Member.MemberNumber 
INNER JOIN 
    DVDTitle 
    ON DVDCopy.DVDNumber = DVDTitle.DVDNumber
WHERE  
    (DVDCopy.CopyNumber = @CopyNumber) 

错误是:

There was an error executing the query. Please check the syntax of the command and if present, the types and values of the parameters and ensure they are correct. Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.

预期结果应根据副本编号从基于下拉列表的 GridView 中的借出表和 DVD 副本表返回特定 DVD 的最后一次借阅(此处选择副本编号)。任何想法，谢谢。

Answer 1

如何将 SELECT 中的子查询移至 INNER JOIN:

SELECT 
    DVDTitle.DVDTitle, 
    Loan.LoanNumber, 
    Member.MemberNumber, 
    Member.MemberFirstName, 
    Member.MemberLastName, 
    mdo.MaxDateOut, 
    Loan.DateDue, Loan.DateReturned
FROM 
    DVDCopy 
INNER JOIN 
    Loan 
    ON DVDCopy.CopyNumber = Loan.CopyNumber 
INNER JOIN 
    Member 
    ON Loan.MemberNumber = Member.MemberNumber 
INNER JOIN 
    DVDTitle 
    ON DVDCopy.DVDNumber = DVDTitle.DVDNumber
INNER JOIN
(SELECT CopyNumber,max(DateOut) as "MaxDateOut" FROM Loan, DVDCopy GROUP BY DVDCopy.CopyNumber) mdo 
    ON DVDCopy.CopyNumber = mdo.CopyNumber
WHERE  
    (DVDCopy.CopyNumber = @CopyNumber)