一条语句从多表中求汇总数据

表A(進料):
PN   Qty  Uom
M1   20   PC
M1   30   PC
M2   40   PC
M3   50   PC
表B(出料):
PN   Qty  Uom
M1   3    PC
M1   5    PC
M3   8    PC
表C(進機):
PN   Qty  Uom
P1   3    PC  
P1   2    PC
P2   10   PC
表D(出機):
PN   Qty  Uom
P1    2   PC  
P2    4   PC
表E(BOM):
PN    SubPN  Qty  Uom
P1     M1     3    PC
P1     M2     2    PC
P2     M1     4    PC
P2     M3     5    PC

要求：根據輸入的物料(如M1)查詢出其對應的進料、出料、進機折算的料件、出機折算的料件

如：
PN    進料　　出料　　進機折算的料件　　出機折算的料件　　   單位　
M1    50       8       55                 22                  PC


謝謝！

 

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

 

select
    a1.PN,
    a1.JL           as 進料,
    isnull(a2.CL,0) as 出料,
    isnull(a3.JJ,0) as 進機,
    isnull(a4.CJ,0) as 出機,
    a1.Uom
from
    (select PN,Uom,SUM(Qty) as JL from A group by PN,Uom) a1
left join
    (select PN,Uom,SUM(Qty) as CL from B group by PN,Uom) a2
on a1.PN=a2.PN and a1.Uom=a2.Uom
left join
    (select PN,Uom,SUM(Qty) as JJ from C group by PN,Uom) a3
on a1.PN=a3.PN and a1.Uom=a3.Uom
left join
    (select PN,Uom,SUM(Qty) as CJ from D group by PN,Uom) a4
on a1.PN=a4.PN and a1.Uom=a4.Uom
order by
   a1.PN,a1.Uom

 

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

 

 

SELECT  a1.PN
       ,a1.JL           AS 進料
       ,ISNULL(a2.CL,0) AS 出料
       ,(SELECT SUM(E.Qty*C.Qty) FROM C INNER JOIN E ON E.PN = C.PN WHERE E.SubPN = a1.PN)AS 進機折算的料件
       ,(SELECT SUM(E.Qty*D.Qty) FROM D INNER JOIN E ON E.PN = D.PN WHERE E.SubPN = a1.PN)AS 出機折算的料件
       ,a1.Uom AS 單位
FROM
    (SELECT PN,Uom,SUM(Qty) AS JL FROM A GROUP BY PN,Uom) a1
LEFT JOIN
    (SELECT PN,Uom,SUM(Qty) AS CL FROM B GROUP BY PN,Uom) a2 ON a1.PN=a2.PN AND a1.Uom=a2.Uom
WHERE a1.PN = 'M1'
ORDER BY
   a1.PN,a1.Uom