mysql - 使用group by时的条件计算

Question

我有 3 个表 order、orderitem、shipment。我必须计算所有这些元素的数量和 MRP这是下面的查询

    SELECT
  DATE_FORMAT(order.orderdate,"%Y-%m%-%d") AS i_orderdt,
  COUNT(DISTINCT order.orderid) AS i_orderscount,
  COUNT(DISTINCT orderitem.orderitemid) as i_orderitemcount,
  COUNT(DISTINCT shipment.shipmentid) AS i_shipmentcount,
  ROUND(SUM(ifnull(orderitem.unitprice,0) * orderitem.quantity),0) AS i_mrp,
FROM order
    LEFT JOIN shipment
        ON shipment.orderid = order.orderid 
    JOIN orderitem
        ON orderitem.orderid = order.orderid

    WHERE order.orderdate >= "2014-01-01 00:00:00" AND order.orderdate <= "2014-03-31 23:59:59" )  GROUP BY DATE_FORMAT(`order`.`orderdate`,"%y-%m%-%d")

但是 MRP 出现错误，因为订单到发货是一对多的关系。我如何编写查询以便计算仅针对不同的 orderitemid 进行？

Answer 1

一种解决方案是将发货计数移至子查询中:

SELECT
  DATE_FORMAT(order.orderdate,"%Y-%m%-%d") AS i_orderdt,
  COUNT(DISTINCT order.orderid) AS i_orderscount,
  COUNT(DISTINCT orderitem.orderitemid) as i_orderitemcount,
  COALESCE(
  (
    SELECT COUNT(DISTINCT shipmentid) 
    FROM shipment 
    WHERE shipment.orderid = order.orderid 
  ), 0) AS i_shipmentcount,
  ROUND(SUM(ifnull(orderitem.unitprice,0) * orderitem.quantity),0) AS i_mrp,
FROM order
JOIN orderitem ON orderitem.orderid = order.orderid
WHERE order.orderdate >= "2014-01-01 00:00:00" 
AND order.orderdate <= "2014-03-31 23:59:59" ) 
GROUP BY DATE_FORMAT(order.orderdate,"%y-%m%-%d");