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

出自TSK WIKI
跳至導覽 跳至搜尋
Root​(留言 | 貢獻)
Root​(留言 | 貢獻)
 
(未顯示同一使用者於中間所作的 2 次修訂)
第9行: 第9行:
==UNION ALL==
==UNION ALL==
<sql>
<sql>
SELECT MD003,MB002,SMC007,AMOUNT FROM(SELECT MD003,MB002, SUM(TH008) AS AMOUNT FROM(SELECT MD003,TH008 FROM  (SELECT MD003 FROM INVMB  
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 (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
JOIN BOMMD ON MB001=MD001
WHERE  MB031 <'0' AND MD017<>'4' GROUP BY MD003)Y
WHERE  MB031 <'0' AND MD017<>'4' GROUP BY MD003)Y
JOIN v_PREINOUT ON TH004=MD003
JOIN v_PREINOUT ON TD004=MD003
WHERE TG003 BETWEEN '20160101' AND '20160301'
WHERE TD013 BETWEEN '20160101' AND '20160301'
UNION ALL
UNION ALL
SELECT MD003,(NUM*MD006/MD007*-1)AS TH008 FROM INVMB
SELECT MD003,(NUM*MD006/MD007*-1)AS TH008 FROM INVMB
第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>