mysql 相乘,MySQL的：如何求和相乘的部分

Having tables like:

sold_items:

code | size | quantity

abc123 | small | 4

abc123 | medium | 2

xyz987 | small | 3

xyz987 | medium | 1

xyz987 | large | 2

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

解决方案

This should work:

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