主页 / dba / 问题 / 161315
Accepted
Fury
Asked: 2017-01-18 05:36:50 +0800 CST

如何更新最终关联记录

  • 772

我有items属于的列表orders。每个item都有fee_percentage字段。和的总和items在表中total_price。orders

我想达到什么目的？我想更新fee最后关联项目的字段。

_________________________      __________________________________________
| order ID | total_price|      | id | order_id | fee_percentage |  fee  |
-------------------------      ------------------------------------------
|   334    |     425    |      |  1 |   334    |      50        |  212  |
  # The `fee` should be 213 => |  2 |   334    |      50        |  212  |

如您所见，总和items将为 424。我想将剩余的费用添加到最后一项fee。（所以item id 2的费用应该是213）。

有什么帮助吗？

这是我到目前为止所写的

UPDATE items AS i
LEFT JOIN orders AS o ON o.order_id = b.order_id
LEFT JOIN (
    SELECT order_id, SUM(fee) AS sum_fee
    FROM items 
    GROUP BY order_id
    WHERE deleted = 0
) AS b1 ON b1.order_id = b.order_id
SET b.fee = b.fee + (o.total_price - b1.sum_fee)
WHERE b.deleted = 0;
mysql

1 个回答

  1. Best Answer

    在这里检查：http ://rextester.com/MGKS16637

    我从表中得到总价和总价max(Id)之间的差异。如果此值为 <> 0，则使用 更新项目。sum(fee)itemsid=max(id)

    update items
       join (select max(i.id) mid, o.totalPrice - sum(i.fee) as diff
           from items i
           inner join orders o on i.order_Id = o.orderId
           group by i.order_id
           order by i.order_id, i.id) v
       on items.id = v.mid
set items.fee = items.fee + v.diff
where v.diff <> 0;
    • 1

相关问题