我正在开发一个贷款系统，在贷款到期时，钱和利息从借款人的帐户转移到贷款人。然而，这只能发生在借款人有足够的钱这样做，否则到目前为止什么也没有发生。
我有一张table，上面有不同的用户和他们的现金。

USERS

+--id--+--name--+--group--+--cash-+

还有一张上面写着每一笔贷款的细节。

LOANS

+--LoanId--+--Lender--+--lenderId--+--Borrower--+--borrowerId--+--principal--+--rate--+--term-+--due_date--+

我从借款人（如果借款人有足够的钱）扣除款项的代码是：

$sql = "UPDATE users AS u
        INNER JOIN (
    SELECT l.borrowerId, SUM(l.principal + ((l.principal*l.rate*l.term)/1200)) AS total_due
    FROM loans AS l
    WHERE l.due_date = '2019-03-24'
    GROUP BY l.borrowerId
                    ) AS d
        ON d.borrowerId = u.id
        SET u.cash = CASE WHEN u.cash > d.total_due THEN u.cash - d.total_due ELSE u.cash END";

它工作得很好，但当我试图从借方扣除并向贷方加钱时，我发现u.cash>d.total\u due的问题，因为它可能同时涉及贷方和借方。
我试过这个，但不起作用：

$sql = "UPDATE users AS u
        INNER JOIN (
    SELECT l.lenderId, SUM(l.principal + ((l.principal*l.rate*l.term)/1200)) AS total_due
    FROM loans AS l
    WHERE l.due_date = '2019-03-24'
    GROUP BY l.lenderId
                    ) AS d
        ON d.lenderId = u.id

        INNER JOIN (
    SELECT l.borrowerId
    FROM loans AS l
    WHERE l.LoanId = d.LoanId
    GROUP BY l.lenderId
    ON d.borrowerId = u.id
                   ) AS b

        SET u.cash = CASE WHEN b.cash > d.total_due THEN u.cash + d.total_due ELSE u.cash END";

我将非常感谢你在这方面的帮助。提前谢谢！