我做了一个查询,里面有一个条件更新。

UPDATE tbl_delivery SET amount_paid = 700000, amount_remaining = CASE WHEN 50000 < 700000 THEN 0 ELSE 50000 - 700000 END, transaction_status = IF(amount_remaining = 0, transaction_status = 'accepted', transaction_status='pending'), payment_status = CASE WHEN amount_remaining = 0 THEN payment_status = 'paid' ELSE payment_status = 'unpaid' END, modified = '2017-12-05 12:14:02' WHERE id = '1' AND user_id = '201720000003'

UPDATE tbl_delivery SET amount_paid = 700000, amount_remaining = CASE WHEN 50000 < 700000 THEN 0 ELSE 50000 - 700000 END, transaction_status = CASE WHEN amount_remaining = 0 THEN transaction_status = 'accepted' ELSE transaction_status = 'pending' END, payment_status = CASE WHEN amount_remaining = 0 THEN payment_status = 'paid' ELSE payment_status = 'unpaid' END, modified = '2017-12-05 12:01:17' WHERE id = '1' AND user_id = '201720000003'


这是我测试以获得正确结果的查询,但是发生的是没有得到我想要获得的结果。

我的问题在于这个领域

    transaction_status = CASE WHEN amount_remaining = 0 THEN transaction_status = 'accepted' ELSE transaction_status = 'pending' END,
 transaction_status = IF(amount_remaining = 0, transaction_status = 'accepted', transaction_status='pending'),


而不是得到结果acceptedpending我得到的是0。

最佳答案

您只需要这个:

transaction_status = CASE WHEN amount_remaining = 0 THEN 'accepted' ELSE 'pending' END


也就是说,您不必重复“ transaction_status =“

UPDATE tbl_delivery
SET
        amount_paid = 700000
      , amount_remaining = CASE WHEN 50000 < 700000 THEN 0 ELSE 50000 - 700000 END
      , transaction_status = IF(amount_remaining = 0, 'accepted','pending')
      , payment_status = CASE WHEN amount_remaining = 0 THEN 'paid' ELSE 'unpaid' END
      , modified = '2017-12-05 12:14:02'
WHERE id = '1'
AND user_id = '201720000003'


就我个人而言,我更喜欢使用case expressions,并且我不鼓励在同一查询中使用IF()和CASE,我认为这很令人困惑。

10-07 15:59
查看更多