「暫存SQL碼」:修訂間差異

出自TSK WIKI
跳至導覽 跳至搜尋
Root​(留言 | 貢獻)
新頁面: ==領料單列印== *20160514 以PDO取值 <sql> SELECT DENSE_RANK() OVER (ORDER BY TE002) AS ROWID ,X.*,TB004,CAST(TB004/TA015 AS FLOAT) AS EACHQ FROM (SELECT TA001,TA002,TD005,TE001...
 
Root​(留言 | 貢獻)
 
(未顯示同一使用者於中間所作的 6 次修訂)
第11行: 第11行:
JOIN MOCTB ON TB001=TE011 AND TB002=TE012 AND TB003=TE004 WHERE TE019<>'V'  
JOIN MOCTB ON TB001=TE011 AND TB002=TE012 AND TB003=TE004 WHERE TE019<>'V'  
ORDER BY TE004
ORDER BY TE004
</SQL>
==查詢訂單內含有某電線==
<SQL>
SELECT TC003,TD004,TD005,TD006,TD041,MD003,TD008,MD006/MD007*TD008 AS AMOUNT
FROM COPTD
JOIN COPTC ON TC001=TD001 AND TC002=TD002
JOIN BOMMD ON MD001=TD004
WHERE TD021='Y' AND MD003 LIKE 'TSS-283%'
</SQL>
==查詢逾期未交之訂單及金額==
<SQL>
SELECT MV002,TC075,
(SELECT CASE WHEN COPTD.UDF02<>'' THEN COPTD.UDF02 ELSE TC012 END)TT,
TD013,TD001,TD002,TD003,TD004,TD008,ISNULL(CAST(TA015 AS CHAR),'')TA015,ISNULL(CAST(TA017 AS CHAR),'')TA017,TD009,
                TD008-TD009 AS DF008,TD011*(TD008-TD009)*TC009 AS DFMONEY,TH001,TH002,TH003,TH008,TH020,TJ001,TJ002,TJ003,TJ007
FROM COPTD JOIN COPTC ON TC001=TD001 AND TC002=TD002
JOIN COPMA ON MA001=TC004
JOIN CMSMV ON MV001=MA016
LEFT JOIN MOCTA ON TA001='5102' AND TA002=TD054 AND TA013='Y'
LEFT JOIN COPTJ ON TJ018=TD001 AND TJ019=TD002 AND TJ020=TD003
LEFT JOIN COPTH ON TH014=TD001 AND TH015=TD002 AND TH016=TD003
WHERE MA019>=401 AND TD016='N'  AND TD021='Y' AND TD013<'20170400' AND TC004<>'0518' AND TD013<'20170101' AND TC004<>'N306'
                AND (TJ021 IS NULL OR TJ021='Y') AND (TH020 IS NULL OR TH020<>'V')
ORDER BY MA016,TC075,TC013,TD001,TD002,TD004
</SQL>
==查詢未結案訂單內含某元件==
<SQL>
SELECT  TC003,TD013,TA520,TA521,MD006/MD007*TD008 AS NUMB
FROM COPTD
JOIN COPTC ON TC001=TD001 AND TC002=TD002
JOIN BOMMD ON MD001=TD004
JOIN MOCTA ON TA001='5102' AND TA002=TD054
WHERE TD021='Y' AND TD016='N' AND TC004='0437' AND MD003='TSS-182A-3P'
</SQL>
==查詢大榮出貨月份合計數量==
<SQL>
SELECT SUBSTRING(SDATE,1,5)MONTH,RCVTX,SUM(CAST(RNUM AS NUMERIC)) 數量 FROM TJOIN WHERE
RNUM > 0 AND RCVTX LIKE '台中精機%' AND SDATE BETWEEN '1070701' AND '1070831'
GROUP BY RCVTX,SUBSTRING(SDATE,1,5) ORDER BY  RCVTX
</SQL>
</SQL>

於 2018年9月6日 (四) 15:19 的最新修訂

領料單列印

  • 20160514 以PDO取值

<sql> SELECT DENSE_RANK() OVER (ORDER BY TE002) AS ROWID ,X.*,TB004,CAST(TB004/TA015 AS FLOAT) AS EACHQ FROM (SELECT TA001,TA002,TD005,TE001,TE002,TE003,TE004,TE017,TA015,TE005,TD013,TA520,TA526, TC004,TE019,TA011,TA013,TE010,TE011,TE012,TC075 AS MA002 FROM MOCTA JOIN MOCTE ON TA001=TE011 AND TA002=TE012 JOIN INVMB ON MB001=TE004 JOIN v_COPTCD ON TA001='5102' AND TA002=TD054 WHERE TE001= '5403' AND MB007<>'302' AND TA001='5102' AND TA002 LIKE '%150811006%')X JOIN MOCTB ON TB001=TE011 AND TB002=TE012 AND TB003=TE004 WHERE TE019<>'V' ORDER BY TE004 </SQL>

查詢訂單內含有某電線

<SQL> SELECT TC003,TD004,TD005,TD006,TD041,MD003,TD008,MD006/MD007*TD008 AS AMOUNT FROM COPTD JOIN COPTC ON TC001=TD001 AND TC002=TD002 JOIN BOMMD ON MD001=TD004 WHERE TD021='Y' AND MD003 LIKE 'TSS-283%' </SQL>

查詢逾期未交之訂單及金額

<SQL> SELECT MV002,TC075, (SELECT CASE WHEN COPTD.UDF02<> THEN COPTD.UDF02 ELSE TC012 END)TT, TD013,TD001,TD002,TD003,TD004,TD008,ISNULL(CAST(TA015 AS CHAR),)TA015,ISNULL(CAST(TA017 AS CHAR),)TA017,TD009,

               TD008-TD009 AS DF008,TD011*(TD008-TD009)*TC009 AS DFMONEY,TH001,TH002,TH003,TH008,TH020,TJ001,TJ002,TJ003,TJ007

FROM COPTD JOIN COPTC ON TC001=TD001 AND TC002=TD002 JOIN COPMA ON MA001=TC004 JOIN CMSMV ON MV001=MA016 LEFT JOIN MOCTA ON TA001='5102' AND TA002=TD054 AND TA013='Y' LEFT JOIN COPTJ ON TJ018=TD001 AND TJ019=TD002 AND TJ020=TD003 LEFT JOIN COPTH ON TH014=TD001 AND TH015=TD002 AND TH016=TD003 WHERE MA019>=401 AND TD016='N' AND TD021='Y' AND TD013<'20170400' AND TC004<>'0518' AND TD013<'20170101' AND TC004<>'N306'

               AND (TJ021 IS NULL OR TJ021='Y') AND (TH020 IS NULL OR TH020<>'V')

ORDER BY MA016,TC075,TC013,TD001,TD002,TD004 </SQL>

查詢未結案訂單內含某元件

<SQL> SELECT TC003,TD013,TA520,TA521,MD006/MD007*TD008 AS NUMB FROM COPTD JOIN COPTC ON TC001=TD001 AND TC002=TD002 JOIN BOMMD ON MD001=TD004 JOIN MOCTA ON TA001='5102' AND TA002=TD054 WHERE TD021='Y' AND TD016='N' AND TC004='0437' AND MD003='TSS-182A-3P' </SQL>

查詢大榮出貨月份合計數量

<SQL> SELECT SUBSTRING(SDATE,1,5)MONTH,RCVTX,SUM(CAST(RNUM AS NUMERIC)) 數量 FROM TJOIN WHERE RNUM > 0 AND RCVTX LIKE '台中精機%' AND SDATE BETWEEN '1070701' AND '1070831' GROUP BY RCVTX,SUBSTRING(SDATE,1,5) ORDER BY RCVTX </SQL>