从另一个表返回一条记录,每个transactionDate有多条记录

我想你想要一个 join 然后过滤以获取最近的日期:

select l.*, s.*
from Loan l join
     Soa s 
     on s.employeeid = l.employeeid
where s.TransactionDate <= (select max(s2.TransactionDate)
                       from Soa s2
                       where s2.employeeid = l.employeeid and
                             s2.TransactionDate <= l.LoanApplicationDate 
                      );

我认为你需要设置加入的条件:

select  
  L.LoanID,
  L.EmployeeID,
  L.LoanAmount,
  L.LoanApplicationDate,
  S.LoanableAmount,
  S.TransactionDate
from Loan L join Soa S
ON S.EmployeeID = L.EmployeeID and
S.TransactionDate = (select max(TransactionDate) from Soa where TransactionDate <= L.LoanApplicationDate)

看到了吗 demo

SQL DEMO

select * 
from Loan L
join Soa S
  ON S.EmployeeID = L.EmployeeID 
 and S.TransactionDate = 
        (select max(S2.TransactionDate) 
         from Soa S2
         where S2.TransactionDate <= L.LoanApplicationDate
           and S2.`EmployeeID` = L.`EmployeeID`
        )

下面的查询将为您提供所有在最新日期提取可贷金额的员工ID

     Select employee_id, 
       LoanableAmount 
      transactionDate from table 
     having
  transactionDate=max(transactionDate)
    group by employee_id

您需要为每一行运行一个子查询:

select q.*, S.LoanableAmount, S.TransactionDate from
   (select  
     L.*
     ,(select SoaID from Soa  x where x.EmployeeID = L.EmployeeID and x.TransactionDate <= L.LoanApplicationDate order by TransactionDate DESC limit 1) as SoaID 
   from Loan L) q
join Soa S on S.SoaID = q.SoaID

第4行将为您提供Soa表中最新日期小于或等于应用程序日期的行的SoaID,然后使用该ID连接并显示其余字段。