「計算OKUMA元件用量」:修訂間差異

出自TSK WIKI
跳至導覽 跳至搜尋
Root​(留言 | 貢獻)
Root​(留言 | 貢獻)
 
(未顯示同一使用者於中間所作的 1 次修訂)
第24行: 第24行:
GROUP BY MD003,MB002)A
GROUP BY MD003,MB002)A
JOIN v_INVMC ON MC001=MD003
JOIN v_INVMC ON MC001=MD003
</sql>
==程式碼==
<sql>
SELECT RTRIM(MB001)AS MB001,MB002,SMC007,THEIN,THEOUT*-1 AS THEOUT,SMC007+THEIN+THEOUT AS SIO
FROM(SELECT MD003,SUM(THEIN)AS THEIN,SUM(THEOUT)AS THEOUT
FROM (SELECT MD003,(SELECT CASE WHEN TD008>='0' THEN TD008 END)AS THEIN,(SELECT CASE WHEN TD008<'0' THEN TD008 END)AS THEOUT
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 TD004=MD003
WHERE TD013  BETWEEN '20160101' AND '20160301'
UNION ALL
SELECT MD003,'0' AS THEIN,(NUM*MD006/MD007*-1)AS THEOUT 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')A GROUP BY MD003)B
JOIN INVMB ON MB001=MD003
JOIN v_INVMC ON MD003=MC001
</sql>
</sql>

於 2015年12月14日 (一) 13:53 的最新修訂

程式碼

<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(TD008) AS AMOUNT FROM(SELECT MD003,TD008 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 TD004=MD003 WHERE TD013 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> SELECT RTRIM(MB001)AS MB001,MB002,SMC007,THEIN,THEOUT*-1 AS THEOUT,SMC007+THEIN+THEOUT AS SIO FROM(SELECT MD003,SUM(THEIN)AS THEIN,SUM(THEOUT)AS THEOUT FROM (SELECT MD003,(SELECT CASE WHEN TD008>='0' THEN TD008 END)AS THEIN,(SELECT CASE WHEN TD008<'0' THEN TD008 END)AS THEOUT

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 TD004=MD003 WHERE TD013 BETWEEN '20160101' AND '20160301'

UNION ALL

SELECT MD003,'0' AS THEIN,(NUM*MD006/MD007*-1)AS THEOUT 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')A GROUP BY MD003)B JOIN INVMB ON MB001=MD003 JOIN v_INVMC ON MD003=MC001 </sql>