MySQL的:如何求和相乘的部分 [英] mysql: how to sum multiplied partials

问题描述

具有类似的表格:

sold_items:

sold_items:

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

price_list:

price_list:

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

这篇关于MySQL的:如何求和相乘的部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！