MySQL 使用具有多个返回行的子查询更新字段值

Question

我有两个表“银行”和“债券”。每个用户都有一条银行记录，但可以有 0、1 或更多债券记录。

我想编写一个脚本，用用户可能持有的多种债券的利息来更新“银行”表中的“现金”字段。利息由“bonds”表的issuePrice * coupon字段计算。但由于用户可能持有多个债券，因此应该对每个债券执行此操作。

此刻，我尝试了这样的事情:

$MySQL->db_Query("UPDATE bonds bo, bank ba SET 
        ba.cash = ROUND(ba.cash + (bo.issuePrice * bo.coupon), 2), 
        ba.earned = ROUND(ba.earned + (bo.issuePrice * bo.coupon), 2) 
    WHERE LOWER(ba.user) = LOWER(bo.holder) AND 
        LOWER(bo.holder) <> LOWER('Bank');");

但它没有给出预期的结果。我对拥有 2 个债券的用户进行了尝试，如果两个债券各应提供 500 利息，那么总共 1000，它只增加 500，就像只有 1 个债券一样。如果我将一种债券设置为 500 利息，另一种债券的计算利息为 1000，它会突然增加 475。

Answer 1

确保您的 UPDATE 语句尝试仅更新每个用户的行一次可能是值得的。子查询是执行此操作的最佳方法，最有效地实现为连接表:

UPDATE bank 
JOIN (SELECT LOWER(bonds.holder) as user,
    SUM(bonds.issuePrice * bonds.coupon) as total
    FROM bonds
    WHERE LOWER(bonds.holder) != 'bank'
    GROUP BY user
) as increments ON increments.user = LOWER(bank.user)
SET bank.cash = ROUND(bank.cash + increments.total, 2),
    bank.earned = ROUND(bank.earned + increments.total, 2)

(为了获得更多优化，LOWER 和 ROUND 调用可能应该被消除，但这是另一个讨论。)