sql - 从多个表中选择 SUM

Question

当我连接 3 个表时，我总是得到错误的总和值。这是表的 ERD 的图片:

(原文在这里:http://dl.dropbox.com/u/18794525/AUG%207%20DUMP%20STAN.png)

这里是查询:

select SUM(gpCutBody.actualQty) as cutQty   , SUM(gpSewBody.quantity) as sewQty
from jobOrder
inner join gpCutHead on gpCutHead.joNum = jobOrder.joNum
inner join gpSewHead on gpSewHead.joNum = jobOrder.joNum
inner join gpCutBody on gpCutBody.gpCutID = gpCutHead.gpCutID
inner join gpSewBody on gpSewBody.gpSewID = gpSewHead.gpSewID

Answer 1

如果您只对所有订单的裁剪和缝纫数量感兴趣，最简单的方法如下:

select (select SUM(gpCutBody.actualQty) from gpCutBody) as cutQty, 
       (select SUM(gpSewBody.quantity) from gpSewBody) as sewQty

(假设剪裁和缝纫总是有关联的工作订单。)

如果您想按工单查看剪裁和缝纫的明细，这样的内容可能更可取:

select joNum, SUM(actualQty) as cutQty, SUM(quantity) as sewQty
from (select joNum, actualQty, 0 as quantity
      from gpCutBody
      union all
      select joNum, 0 as actualQty, quantity
      from gpSewBody) sc
group by joNum