「優化多訂單多工單作業」:修訂間差異
跳至導覽
跳至搜尋
新頁面: ==相關TABLE== *COPTD 客戶訂單單身資料檔 **TD001 單別 **TD002 單號 **TD003 序號 **TD004 品號 **TD005 品名 **TD008 訂單數量 * BOMMD BOM用量資料單身檔欄... |
|||
| 第27行: | 第27行: | ||
**SELECT MD003,MD006 FROM COPTD LEFT JOIN BOMMD ON TD004=MD001 WHERE TD001='2201' AND TD002='150113001' | **SELECT MD003,MD006 FROM COPTD LEFT JOIN BOMMD ON TD004=MD001 WHERE TD001='2201' AND TD002='150113001' | ||
*查詢多訂單之品名及用量 | *查詢多訂單之品名及用量 | ||
**SELECT (SELECT MB002 FROM INVMB WHERE MB001=MD003)AS MB002,ROUND(SUM(MD006/MD007),2) AS AMOUNT FROM COPTD LEFT JOIN BOMMD ON TD004=MD001 WHERE TD001='2201' AND TD002='150113001' GROUP BY MD003 | **SELECT (SELECT MB002 FROM INVMB WHERE MB001=MD003)AS MB002,ROUND(SUM(MD006/MD007*TD008),2) AS AMOUNT FROM COPTD LEFT JOIN BOMMD ON TD004=MD001 WHERE TD001='2201' AND TD002='150113001' GROUP BY MD003 | ||
於 2015年1月13日 (二) 11:53 的修訂
相關TABLE
- COPTD 客戶訂單單身資料檔
- TD001 單別
- TD002 單號
- TD003 序號
- TD004 品號
- TD005 品名
- TD008 訂單數量
- BOMMD BOM用量資料單身檔欄位名稱
- BOMMD.MD001 主件品號
- BOMMD.MD002 序號
- BOMMD.MD003 元件品號
- BOMMD.MD004 單位
- BOMMD.MD006 組成用量
- BOMMD.MD007 底數
- BOMMD.MD008 損耗率
- BOMMD.MD016 備註
- INVMB 品號基本資料表
- INVMB.MB001 品號
- INVMB.MB002 品名
- INVMB.MB003 規格
- INVMB.MB004 庫存單位
相關SQL查詢
- 依訂單查詢單身品名
- SELECT TD003,TD004,TD005,TD008 FROM COPTD WHERE TD001='2201' AND TD002='150113001'
- 查詢BOM單身內容(元件及用量,以COPTD左JOIN)
- SELECT MD003,MD006 FROM COPTD LEFT JOIN BOMMD ON TD004=MD001 WHERE TD001='2201' AND TD002='150113001'
- 查詢多訂單之品名及用量
- SELECT (SELECT MB002 FROM INVMB WHERE MB001=MD003)AS MB002,ROUND(SUM(MD006/MD007*TD008),2) AS AMOUNT FROM COPTD LEFT JOIN BOMMD ON TD004=MD001 WHERE TD001='2201' AND TD002='150113001' GROUP BY MD003