mysql:如何对相乘的部分求和

2022-01-09 00:00:00 multiplication sum mysql

有如下表格:

已售商品:

code   | size   | quantity 
abc123 | small  | 4
abc123 | medium | 2  
xyz987 | small  | 3
xyz987 | medium | 1
xyz987 | large  | 2

价格表:

code   | size   | price 
abc123 | small  | 5
abc123 | medium | 7  
abc123 | large  | 9 
xyz987 | small  | 10
xyz987 | medium | 13
xyz987 | large  | 15

这将是您获得的最佳(更快的查询)方法

结果:

which would be your best (faster query) approach to get
results:

code | sold_items | cash abc123 | 6 | 34 xyz987 | 6 | 73

推荐答案

这应该可行:

SELECT si.code, SUM(si.quantity), SUM(si.quantity * pl.price) as cash FROM sold_items si INNER JOIN price_list pl ON pl.code = si.code AND pl.size = si.size GROUP BY si.code

相关文章