現在有這樣一句SQL陳述句
SELECT * FROM (SELECT MCODE, STORAGEID, SUM(STORAGEQTY) AS QTY
FROM TBLSTORAGEINFO
WHERE STORAGEQTY != 0
GROUP BY MCODE, STORAGEID) A
INNER JOIN (SELECT C.MCODE, C.STORAGEID, SUM(C.LOTQTY) AS QTY
FROM TBLSTORAGELOTINFO c
inner join tblitemlot D
ON D.LOTNO = C.LOTNO
WHERE STORAGEID NOT LIKE '%GB%'
GROUP BY C.MCODE, C.STORAGEID) B
ON A.MCODE = B.MCODE
AND A.STORAGEID = B.STORAGEID
AND A.QTY != B.QTY
結果如下
現在需要多連接兩張表
一張XITEM表中有一個換算系數KANZANK,當A和B的MCODE=XITEM的CODE時，A.QTY和B.QTY需要除以KANZANK
另一張表XZAIK，有兩個欄位HOKAN和ZAIK，HOKAN也需要NOT LIKE '%GB%'，ZAIK也需要除以KANZANK
希望在結果表中把XAIK的CODE ,HOKAN,ZAIK也加進去
現在希望得到以下結果
MCODE STORAGEID QTY MCODE STORAGEID QTY CODE HOKAN ZAIK
1 8021740(I) BS02 8.5 8021740(I) BS02 8.505 8021740(I) BS02 8.5
2 5518010(I) BS02 8812 5518010(I) BS02 8632 5518010(I) BS02 8812
大概就是這樣,可能我表達的不是很清楚。自己都快被繞暈了
uj5u.com熱心網友回復：
就在直接關聯不行嗎，像下面這樣
SELECT * FROM (SELECT MCODE, STORAGEID, SUM(STORAGEQTY) AS QTY
FROM TBLSTORAGEINFO
WHERE STORAGEQTY != 0
GROUP BY MCODE, STORAGEID) A
INNER JOIN (SELECT C.MCODE, C.STORAGEID, SUM(C.LOTQTY) AS QTY
FROM TBLSTORAGELOTINFO c
inner join tblitemlot D
ON D.LOTNO = C.LOTNO
WHERE STORAGEID NOT LIKE '%GB%'
GROUP BY C.MCODE, C.STORAGEID) B
ON A.MCODE = B.MCODE
AND A.STORAGEID = B.STORAGEID
AND A.QTY != B.QTY
INNER JOIN XITEM E ON A.MCODE=E.CODE
INNER JOIN XZAIK F ON E.CODE=F.CODE
WHERE HOKAN NOT LIKE '%GB%'
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/269950.html
標籤：基礎類
下一篇：對ASCII檔案的操作