菜鸟求复杂的SQL语句, 怎么写? 多谢
初学者求复杂的SQL语句, 如何写?? 谢谢
sql中有5个表,
A表--JOB,
JOBNO, P_CODE, LOT, QTY
B表--RESVT
P_CODE, CODE, LOT, QTY, DEFA_QTY, QTY1
C表--PCT
PCNO, PDNO, JOBNO, PLOT, P_CODE, QTY2
D表--MATPCT
PCNO, JOBNO, PLOT, P_CODE, CODE, QTY3
E表--MRT
MRNO, JOBNO, CODE, P_CODE, QTY4
需求结果:
JOBNO,PCNO, PDNO,MRNO, P_CODE,CODE, QTY, DEFA_QTY, QTY1,SUM(QTY2), SUM(QTY3), SUM(QTY4)
------解决方案--------------------
楼主，请参考这个代码，如果列对应关系没错的话，那么问题就不大了
sql中有5个表,
A表--JOB,
JOBNO, P_CODE, LOT, QTY
B表--RESVT
P_CODE, CODE, LOT, QTY, DEFA_QTY, QTY1
C表--PCT
PCNO, PDNO, JOBNO, PLOT, P_CODE, QTY2
D表--MATPCT
PCNO, JOBNO, PLOT, P_CODE, CODE, QTY3
E表--MRT
MRNO, JOBNO, CODE, P_CODE, QTY4
需求结果:
JOBNO,PCNO, PDNO,MRNO, P_CODE,CODE, QTY, DEFA_QTY, QTY1,SUM(QTY2), SUM(QTY3), SUM(QTY4)
------解决方案--------------------
select a.JOBNO,e.P_CODE,e.QTY,e.CODE,b.DEFA_QTY,sum(c.QTY) as 'c.QTY' ,sum(d.QTY) as 'd.QTY',sum(e.QTY) as 'e.QTY'
from A表 a
join B表 b on a.JOBNO = b.JOBNO and a.CODE =b.CODE
join C表 c on a.JOBNO = c.JOBNO and a.CODE =c.CODE
join D表 d on a.JOBNO = d.JOBNO and a.CODE =d.CODE
join E表 e on a.JOBNO = e.JOBNO and a.CODE =e.CODE
group by a.JOBNO,e.P_CODE,e.QTY,e.CODE,b.DEFA_QTY
楼主，请参考这个代码，如果列对应关系没错的话，那么问题就不大了