「計算OKUMA元件用量」:修訂間差異
新頁面: ==程式碼== <sql> SELECT MD003,(NUM*MD006/MD007*-1)AS AMOUNT FROM INVMB JOIN (SELECT NAME,SUM(NUM) AS NUM FROM OKUMA WHERE PRDATE BETWEEN '20160101' AND '20160301' GROUP BY NAME)X ON... |
|||
| 第6行: | 第6行: | ||
JOIN BOMMD ON MB001=MD001 | JOIN BOMMD ON MB001=MD001 | ||
WHERE NUM >'0' AND MB031 <'0' AND MD017<>'4' | WHERE NUM >'0' AND MB031 <'0' AND MD017<>'4' | ||
</sql> | |||
==UNION ALL== | |||
<sql> | |||
SELECT MD003,MB002,SMC007,AMOUNT FROM(SELECT MD003,MB002, SUM(TH008) AS AMOUNT FROM(SELECT MD003,TH008 FROM (SELECT MD003 FROM INVMB | |||
JOIN (SELECT NAME FROM OKUMA WHERE PRDATE BETWEEN '20160101' AND '20160301' AND NUM >'0' GROUP BY NAME)X ON MB002=NAME | |||
JOIN BOMMD ON MB001=MD001 | |||
WHERE MB031 <'0' AND MD017<>'4' GROUP BY MD003)Y | |||
JOIN v_PREINOUT ON TH004=MD003 | |||
WHERE TG003 BETWEEN '20160101' AND '20160301' | |||
UNION ALL | |||
SELECT MD003,(NUM*MD006/MD007*-1)AS TH008 FROM INVMB | |||
JOIN (SELECT NAME,SUM(NUM) AS NUM FROM OKUMA WHERE PRDATE BETWEEN '20160101' AND '20160301' AND NUM >'0' GROUP BY NAME)X | |||
ON MB002=NAME | |||
JOIN BOMMD ON MB001=MD001 | |||
WHERE MB031 <'0' AND MD017<>'4')Z | |||
JOIN INVMB ON MB001=MD003 | |||
GROUP BY MD003,MB002)A | |||
JOIN v_INVMC ON MC001=MD003 | |||
</sql> | </sql> | ||
於 2015年12月11日 (五) 22:39 的修訂
程式碼
<sql> SELECT MD003,(NUM*MD006/MD007*-1)AS AMOUNT FROM INVMB JOIN (SELECT NAME,SUM(NUM) AS NUM FROM OKUMA WHERE PRDATE BETWEEN '20160101' AND '20160301' GROUP BY NAME)X ON MB002=NAME JOIN BOMMD ON MB001=MD001 WHERE NUM >'0' AND MB031 <'0' AND MD017<>'4' </sql>
UNION ALL
<sql> SELECT MD003,MB002,SMC007,AMOUNT FROM(SELECT MD003,MB002, SUM(TH008) AS AMOUNT FROM(SELECT MD003,TH008 FROM (SELECT MD003 FROM INVMB JOIN (SELECT NAME FROM OKUMA WHERE PRDATE BETWEEN '20160101' AND '20160301' AND NUM >'0' GROUP BY NAME)X ON MB002=NAME JOIN BOMMD ON MB001=MD001 WHERE MB031 <'0' AND MD017<>'4' GROUP BY MD003)Y JOIN v_PREINOUT ON TH004=MD003 WHERE TG003 BETWEEN '20160101' AND '20160301' UNION ALL SELECT MD003,(NUM*MD006/MD007*-1)AS TH008 FROM INVMB JOIN (SELECT NAME,SUM(NUM) AS NUM FROM OKUMA WHERE PRDATE BETWEEN '20160101' AND '20160301' AND NUM >'0' GROUP BY NAME)X ON MB002=NAME JOIN BOMMD ON MB001=MD001 WHERE MB031 <'0' AND MD017<>'4')Z JOIN INVMB ON MB001=MD003 GROUP BY MD003,MB002)A JOIN v_INVMC ON MC001=MD003 </sql>