日期:2014-05-18  浏览次数:20803 次

SQL查询计算结果多了一倍,求解!
SQL code

-----订单数量统计列表
select     coOrder.OrderID, SUM(coOrderDetail.Qty) AS [订单数量],SUM(stInstoreDetail.Qty) AS [入库数量]
From coOrder
INNER JOIN coOrderDetail     ON     coOrder.OrderNo=coOrderDetail.OrderNo
INNER JOIN pmOrder           ON  coOrder.OrderID =pmOrder.OrderID
INNER JOIN stInstoreDetail   ON  pmOrder.OrderNO=stInstoreDetail.OrderNo

WHERE coOrder.OrderID='20120101'
GROUP BY  coOrder.OrderID
---------------------------------------
orderID    订单数量       入库数量
20120101     4857           10400            实际正确的入库数量应该为:5200




coOrder表为订单主要信息表 ,对应的coOrderDetail为订单明细表有两个产品,即明细行有2行, pmOrder为生产表 ,stInstoreDetail为产品入库明细表。
求解为什么入库数量是实际的2倍呢,是不是订单的明细行有两上产品的原因,如果是这样,该怎么改下查询呢?

------解决方案--------------------
先合计,再关联
SQL code
select     coOrder.OrderID, coOrderDetail.Qty AS [订单数量],stInstoreDetail.Qty AS [入库数量]
From coOrder
INNER JOIN (select OrderNo,SUM(Qty) as Qty from coOrderDetail group by OrderNo) as  coOrderDetail   ON     coOrder.OrderNo=coOrderDetail.OrderNo
INNER JOIN pmOrder           ON  coOrder.OrderID =pmOrder.OrderID
INNER 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