类似于 SQL 中的 forEach ?

Question

所以我需要做一个查询来显示每件商品的售出次数，我有两个表格看起来像这样

Products
+-----------+-------+-----------+
| productID | price | inventory |
+-----------+-------+-----------+
| 31f9d     | 21    | 109       |
| M21xZ     | 80    | 87        |
| 56JmZ     | 35    | 48        |
+-----------+-------+-----------+
orderDetails
+---------+-------+----------+
| orderId | item  | Quantity |
+---------+-------+----------+
| 1       | 31f9d | 2        |
| 2       | 31f9d | 5        |
| 2       | 56JmZ | 3        |
+---------+-------+----------+

提前感谢！

Answer 1

如果您只需要每个产品的销售数量，则可以聚合详细信息表：

select item, count(*) as cnt_sales, sum(quantity) as sum_quantity
from orderdetails
group by item

如果您也想要每个产品的详细信息，那么您可以加入。

select p.*, d.cnt_sales, d.sum_quantity
from products p
left join (
select item, count(*) as cnt_sales, sum(quantity) as sum_quantity
from orderdetails
group by item
) d on d.item = p.productid

该left join允许没有任何销售的产品;它将在cnt_sales列中引入null值，并在结果集中sum_quantity值。

Answer 2

将两个表连接在一起，然后简单地按 itemId 分组：

select p.productID, sum(o.Quantity) from Products p 
left join orderDetails o on p.productID = o.item 
group by p.productID