「NAVICAT SQL指令存檔」:修訂間差異

出自TSK WIKI
跳至導覽 跳至搜尋
Root​(留言 | 貢獻)
Tpl​(留言 | 貢獻)
 
(未顯示由 1 位使用者於中間所作的 2 次修訂)
第1行: 第1行:
==查電線01庫存在小數點==
<SQL>SELECT * FROM (SELECT MM001,MM002,SUM (MM005) AS MM05 FROM INVMM
WHERE MM002 = '01' GROUP BY MM001,MM002 )x WHERE MM05 <> CAST(MM05 AS INT) ORDER BY MM001</SQL>
==INVMB==
==INVMB==
#修正
#修正
第27行: 第31行:
#**SELECT MD001,MD002,MD003,MD004,MD006,MD007,MD032,CREATE_DATE, CREATE_TIME FROM BOMMD WHERE CAST(MD006 AS INT)<>MD006
#**SELECT MD001,MD002,MD003,MD004,MD006,MD007,MD032,CREATE_DATE, CREATE_TIME FROM BOMMD WHERE CAST(MD006 AS INT)<>MD006
#查BOM單身序號最大值
#查BOM單身序號最大值
##WITH cte AS(
select * from BOMMD Y JOIN
  SELECT *,
(select MD001,max(MD002) MD002 from BOMMD group by MD001) X
        ROW_NUMBER() OVER (PARTITION BY MD001 ORDER BY MD002 DESC) AS rn
  ON Y.MD001=X.MD001 AND Y.MD002=X.MD002
  FROM BOMMD
)
  SELECT *
FROM cte
WHERE rn = 1

於 2015年12月29日 (二) 11:15 的最新修訂

查電線01庫存在小數點

<SQL>SELECT * FROM (SELECT MM001,MM002,SUM (MM005) AS MM05 FROM INVMM WHERE MM002 = '01' GROUP BY MM001,MM002 )x WHERE MM05 <> CAST(MM05 AS INT) ORDER BY MM001</SQL>

INVMB

  1. 修正
    • 修正線別
      • UPDATE INVMB SET MB068 = 'WM01' WHERE PATINDEX('%[A-Z]%', MB001)<>0
    • 修正領料碼
      • UPDATE INVMB SET MB042 = '1'
    • 修正庫存單位
      • UPDATE INVMB SET MB004 = 'PCS' WHERE MB004 = '條'
    • 修正使用者群組
      • UPDATE INVMB SET USR_GROUP = '021' WHERE USR_GROUP = 'A0025'
  2. 查詢
    • 查詢品號之領料碼設定
      • SELECT MB001,MB002,MB042 FROM INVMB
    • 查詢資料庫指定欄位中,字串所含特定字元A-Z
      • SELECT [FIELD] FROM [TABLENAME] PATINDEX('%[A-Z]%', [FIELD])=0

MOCTA

  1. 修正
    • 查詢UDF01為無值的欄位並置入*製令-單別單號*
      • SELECT '*'+RTRIM(TA001)+'-'+RTRIM(TA002)+'*' AS MOC FROM MOCTA WHERE UDF01 = AND TA013='N'
      • UPDATE MOCTA SET UDF01='*'+RTRIM(TA001)+'-'+RTRIM(TA002)+'*' WHERE UDF01 = AND TA013='N'
      • TRIM用法:減去字串中空格,由左則用LTRIM,由右則用RTRIM

BOMMD

  1. 修正
    • 判別BOM表中的組成用量是否為小數
      • SELECT MD001,MD002,MD003,MD004,MD006,MD007,MD032,CREATE_DATE, CREATE_TIME FROM BOMMD WHERE CAST(MD006 AS INT)<>MD006
  2. 查BOM單身序號最大值
select * from BOMMD Y JOIN
(select MD001,max(MD002) MD002 from BOMMD group by MD001) X 
ON Y.MD001=X.MD001 AND Y.MD002=X.MD002