求一SQL查询语句
Table A
JOB=JOB001, P-CODE=4711, QTY=1000
Table B
JOB=JOB001, P-CODE=4711, QTY=1000, CODE=4712, D-QTY=150
JOB=JOB001, P-CODE=4711, QTY=1000, CODE=4713, D-QTY=2000
Table C
MTT=TT01, DATE=2013/1/4, JOB=JOB001, P-CODE=4711, CODE=4712, D-QTY=50
MTT=TT02, DATE=2013/1/4, JOB=JOB001, P-CODE=4711, CODE=4713, D-QTY=800
MTT=TT03, DATE=2013/1/5, JOB=JOB001, P-CODE=4711, CODE=4712, D-QTY=120
MTT=TT03, DATE=2013/1/5, JOB=JOB001, P-CODE=4711, CODE=4713, D-QTY=1300
BOM:
P-CODE=4711, CODE=4712, USEAGE=0.15
P-CODE=4711, CODE=4713, USEAGE=2
显示结果:
JOB=JOB001, P-CODE=4711, QTY=1000, CODE=4712, D-QTY=150, D-QTY=170(即Table C的多次D-QTY之和)
JOB=JOB001, P-CODE=4711, QTY=1000, CODE=4713, D-QTY=2000, D-QTY=2100(即Table C的多次D-QTY之和)
[解决办法]
Select
a.JOB
,a.P-CODE
,SUM(a.QTY) As QTY
,b.CODE
,Isnull(SUM(b.D-QTY),0) As D-QTY
,Isnull(SUM(c.D-QTY),0) As D-QTY
From TableA As a
Left Join TableB As b On a.JOB=b.JOB And a.P-CODE=b.P-CODE
Left Join TableC As c On a.JOB=c.JOB And a.P-CODE=c.P-CODE And b.CODE=c.CODE
Group by a.JOB,a.P-CODE,b.CODE
