mysql - 连接中表的列总和

Question

我有 3 个表:Orders、Orders_products 和 Orders_total。目前我有一个查询，可以获取每个月的产品总和，但现在我们还想添加不同表中的运费。

我尝试使用以下命令返回正确的total_value，但total_shipping是原来的5倍。我认为这与订单有关，可以有多种产品，但我不知道还能做什么。

  SELECT Count(DISTINCT O.orders_id)                AS Orders, 
       Sum(OP.final_price * OP.products_quantity) AS total_value, 
       Date_format(O.last_modified, '%m-%Y')      AS date_interval, 
       Sum(OT.value)                              AS total_shipping 
FROM   
       orders AS O 
LEFT JOIN 
        orders_total AS OT 
ON ( OT.orders_id = O.orders_id 
AND OT.class = 'ot_shipping' ), 
       orders_products AS OP 
WHERE  
       ( O.orders_id = OP.orders_id ) 
       AND ( O.orders_status = 3 ) 
GROUP  BY date_interval 
ORDER  BY O.last_modified DESC 

返回值为:

+----+------------+---------------+----------------+
| ID | total_value| date_interval | total_shipping |
+----+------------+---------------+----------------+
| 17 | 55912.2160 |    01-2014    |      24954     |

预期:

+----+------------+---------------+----------------+
| ID | total_value| date_interval | total_shipping |
+----+------------+---------------+----------------+
| 17 | 55912.2160 |    01-2014    |       4938     |

这是sqlfiddle http://sqlfiddle.com/#!2/dfe10/1/0

它包含一个订单，其中包含 3 种产品。预期的total_value 为500，预期的total_shipping 也是500，但返回1500(3 x 产品)。遗憾的是，由于 sqlfiddle 的最大 8000 个字符的限制，我不得不从表中删除很多字段。

Answer 1

尝试将运费值放入内联 View 中:

select count(*) as Orders,
sum(ord.order_total_value) as total_value,
ord.date_interval as date_interval,
sum(ship.order_shipping_value) as total_shipping
from
(
SELECT O.orders_id,
       O.last_modified AS modified_date,
       Sum(OP.final_price * OP.products_quantity) as order_total_value, 
       Date_format(O.last_modified, '%m-%Y') as date_interval
FROM  orders AS O
      INNER JOIN orders_products AS OP on O.orders_id = OP.orders_id
WHERE O.orders_status = 3  
GROUP  BY date_interval,O.orders_id
) ord LEFT OUTER JOIN 
(
 SELECT orders_id,sum(value) as order_shipping_value
 FROM orders_total
 WHERE class='ot_shipping'
 GROUP BY orders_id
) ship ON ord.orders_id = ship.orders_id
GROUP BY ord.date_interval
ORDER BY modified_date DESC;