mysql - SQL 查找商品级别的折扣

Question

我使用MySQL我有一个订单表:

Order  |  Items | Discount

+++++++++++++++++

A      |   04 | 10 

和一个Items表:

Order | Items | Discount

+++++++++++++++++++

A  | 02 |

A  | 01 |  

A  | 01 |

我需要找到每个商品级别的折扣。这将是

2/4 * 10  = 5 for 1st item
1/4 * 10  = 2.5 for 2nd item
1/4 * 10  = 2.5 for 3rd item

需要计算并保存商品表上的折扣。

Answer 1

好的，根据您的评论，您正在寻找类似的内容:

UPDATE Items SET Discount = (SELECT ((I.Items/O.Items) * O.Discount) FROM
    Order O JOIN Items I ON O.Order = Items.Order WHERE O.Order = 'A')
    WHERE Items.Order = 'A';