先合计再关联

 

-----订单数量统计列表select coOrder.OrderID, SUM(coOrderDetail.Qty) AS [订单数量],SUM(stInstoreDetail.Qty) AS [入库数量]

From coOrderINNER

JOIN coOrderDetail ON coOrder.OrderNo=coOrderDetail.OrderNoINNER

JOIN pmOrder ON coOrder.OrderID =pmOrder.OrderIDINNER

JOIN stInstoreDetail ON pmOrder.OrderNO=stInstoreDetail.OrderNo

WHERE coOrder.OrderID='20120101'

GROUP BY coOrder.OrderID

---------------------------------------

orderID 订单数量 入库数量20120101 4857 10400 实际正确的入库数量应该为:5200

 

 

解决方法是先合计，再关联
select coOrder.OrderID, coOrderDetail.Qty AS [订单数量],stInstoreDetail.Qty AS [入库数量]

From coOrderINNER

JOIN (select OrderNo,SUM(Qty) as Qty from coOrderDetail group by OrderNo) as coOrderDetail ON coOrder.OrderNo=coOrderDetail.OrderNoINNER

JOIN pmOrder ON coOrder.OrderID =pmOrder.OrderIDINNER

 JOIN (select OrderNo,SUM(Qty) as Qty from stInstoreDetail group by OrderNo) as stInstoreDetail ON pmOrder.OrderNO=stInstoreDetail.OrderNo

WHERE coOrder.OrderID='20120101'

GROUP BY coOrder.OrderID