mysql - 获取 DBtable 上每个项目的总和并将其与另一个 DBtable 进行比较

Question

我有一个产品表，我想根据我的产品表获取订购产品的总数

这是我的产品表

products
+---------------+-------------------+
| product_id    |    product_name   |
+---------------+-------------------+
|     1         |       ice cream   |
|     2         |        iced tea   |
|     3         |         cake      |
|     4         |   orange juice    |
|     5         |   coconut drink   |
|     5         |   coconut shake   |
+---------------+-------------------+

这是我的 order_items 表

+---------------+-------------------+----------------+---------------+
|   order_id    |    product_name   |   product_qty  |    product_id |
+---------------+-------------------+----------------+---------------+
|     1         |       ice cream   |        5       |      1        |
|     1         |        iced tea   |        10      |      2        |
|     1         |      cake         |        10      |      3        |
|     1         |    orange juice   |        10      |      4        |
|     2         |       ice cream   |        5       |      1        |
|     2         |        iced tea   |        10      |      2        |
|     2         |      cake         |        10      |      3        |
|     2         |    orange juice   |        10      |      4        |
|     3         |       ice cream   |        5       |      1        |
|     3         |        iced tea   |        10      |      2        |
|     3         |      cake         |        10      |      3        |
|     3         |    orange juice   |        10      |      4        |
+---------------+-------------------+----------------+---------------+

我的预期结果是这样的

ice cream      =  15
iced tea       =  30
cake           =  30 
orange juice   =  30
coconut drink  =  0
coconut shake  =  0

我尝试使用此代码作为引用，但我对内部联接不太了解

SELECT t.timeasd 
from   time t 
left join (
          select time_reserved, 
                 date_reservation 
          from   reservations 
          where  date_reservation = '$res_date'
          ) as q 
      on t.timeasd = q.time_reserved 
where q.date_reservation is null 
      AND t.status='active';

Answer 1

你只是想要聚合吗？

select product_name, sum(product_qty) sum_product_qty
from order_items 
group by product_id, product_name

旁注:您的模型未正确标准化。您不应该将产品名称存储在订单表中:此信息属于产品表。您应该仅将产品的 id 存储在订单表中，然后您将通过连接获得结果:

select p.product_name, coalesce(sum(o.product_qty), 0) sum_product_qty
from products p
left join order_items o on p.product_id = o.product_id
group by p.product_id, p.product_name