php - MySQL从具有相同产品的多个记录的两个表中获取列的总和

Question

我的要求是获取生产商品数量和销售商品数量的总和。我的查询是

select productions.created_at, 
       produced.expiry_date,
       items.id as item_id,
       items.quantity as 
       sold_quantity,
       items.product_price as sailing_price,
       sum(produced.quantity) as total_produced,
       sum(items.quantity) as total_sold 
from productions inner join produced_products as produced
on produced.production_id = productions.id 
inner join store_order_items as items 
on items.product_keeping_id = produced.keeping_id 
group by produced.keeping_id, items.product_keeping_id

这个查询返回

[0] => 数组
(
[创建时间] => 2017-07-13 10:25:52
[过期日期] => 2017-07-31
[item_id] => 2
[售出数量] => 1.00
[航行价格] => 120
[total_produced] => 6.000
[total_sold] => 2.00
)

[1] => 数组
(
[创建时间] => 2017-07-07 13:30:25
[过期日期] => 0000-00-00
[item_id] => 3
[售出数量] => 1.00
[航行价格] => 120
[total_produced] => 16.000
[total_sold] => 4.00
)

这些记录是错误的。它应该返回

 [0] (
        [total_sold] => 1
       ),
  [1] (
       [total_sold] => 1
      )

我怎样才能得到正确的结果

Answer 1

试试这个

select productions.created_at, 
produced.expiry_date,
items.id as item_id,
items.quantity as sold_quantity,
items.product_price as sailing_price,
sum(produced.quantity) as total_produced,
(SELECT sum(t1.quantity) FROM `store_order_items` as t1 where t1.product_keeping_id = produced.keeping_id ) as total_sold
from productions inner join produced_products as produced
on produced.production_id = productions.id 
inner join store_order_items as items 
on items.product_keeping_id = produced.keeping_id 
group by produced.keeping_id, items.product_keeping_id