iT邦幫忙

0

SQL TABLE展開

使用 MS SQL

現有一 TABLE_A 欄位資料如下

001

我希望可以用 T-SQL 將 TABLE_A 資料拆開後存於 TABLE_B

002

規則特徵:

  • Start與End欄位資料由前置碼+流水碼組成
  • 前置碼僅存在英文與符號
  • 前置碼沒有固定長度
  • 流水碼僅存在數字
  • 流水碼沒有固定長度
  • 流水碼不一定從1開始
  • 數量反應此行可拆幾組資料

以上是在實務碰到的需求,後來用後端解決了
但是因為寫法實在有點醜,想請教更好的做法/images/emoticon/emoticon06.gif

測試資料提供

DECLARE @TABLE_A AS TABLE
(
   [Start] varchar(20),
   [End] varchar(20),
   [Qty] numeric(10)
)

DECLARE @TABLE_B AS TABLE
(
   [Start] varchar(20),
   [End] varchar(20),
   [Qty] numeric(10)
)

INSERT INTO @TABLE_A VALUES('ABC001','ABC005',5)
INSERT INTO @TABLE_A VALUES('ABC007','ABC007',1)
INSERT INTO @TABLE_A VALUES('AC0002','AC0003',2)
看更多先前的討論...收起先前的討論...
石頭 iT邦高手 1 級 ‧ 2021-01-22 17:29:07 檢舉
前置碼沒有固定長度 在sqlserver parsing字串比較麻煩,有考慮前置碼使用另一個欄未來儲存嗎? 如果沒有在AP那邊做會比較簡單
石頭 iT邦高手 1 級 ‧ 2021-01-22 17:37:16 檢舉
另外會有 表A 前置碼 Start和end 不一致的情況嗎?
微笑 iT邦新手 3 級 ‧ 2021-01-22 17:51:35 檢舉
不會,同一行的Start與End規則會一致

是有欄位C可以另外關聯TABLE,分別帶回只有前置碼與流水碼長度的欄位
例 : ABC001 ABC005 5 這行
可以關聯出 ABC 與 3 這兩個欄位資料

如果最終結果有使用到此關聯也可接受
石頭 iT邦高手 1 級 ‧ 2021-01-22 17:55:39 檢舉
我會建議前置號分開存
1.避免start和end不一致
2.寫法可以更好寫 不用太多function
1
石頭
iT邦高手 1 級 ‧ 2021-01-22 17:53:21
最佳解答

就如果留言說的我會建議你資料格式調整一下,如果不調整AP坐起來會比較有效率

因為你目前需要使用大量function,會造成index失效(只能table scan)

使用CTE遞迴搭配下面的function應該可以完成你的需求

DECLARE @TABLE_A AS TABLE
(
   [Start] varchar(20),
   [End] varchar(20),
   [Qty] numeric(10)
)

DECLARE @TABLE_B AS TABLE
(
   [Start] varchar(20),
   [End] varchar(20),
   [Qty] numeric(10)
)

INSERT INTO @TABLE_A VALUES('ABC001','ABC005',5)
INSERT INTO @TABLE_A VALUES('ABC007','ABC007',1)
INSERT INTO @TABLE_A VALUES('AC0002','AC0003',2)


;WITH CTE  AS (
	SELECT SUBSTRING([Start],0,PATINDEX('%[0-9]%', [Start])) as prefix,
		   CAST(SUBSTRING([Start],PATINDEX('%[0-9]%', [Start]),LEN([Start]) - PATINDEX('%[0-9]%', [Start]) + 1) AS INT) fromNum,
		   CAST(SUBSTRING([End],PATINDEX('%[0-9]%', [End]),LEN([End]) - PATINDEX('%[0-9]%', [End]) + 1) AS INT) toNum,
		    PATINDEX('%[1-9]%', [Start]) - PATINDEX('%[0-9]%', [Start])  paddingOfZero
	FROM @TABLE_A
	UNION ALL
	SELECT  prefix,
			fromNum +1,
	        toNum,
			paddingOfZero
	FROM CTE 
	WHERE fromNum +1<=toNum
) 
INSERT INTO @TABLE_B ([Start],[End],Qty)
select CONCAT(prefix,FORMAT(fromNum,REPLICATE('0',paddingOfZero + 1))),
	   CONCAT(prefix,FORMAT(fromNum,REPLICATE('0',paddingOfZero + 1))),
	   1
from cte


SELECT *
FROM @TABLE_B

Qty那邊你沒特別說邏輯 我就針對你提問的流水號寫語法,

dbfiddle

關於CTE-RECURSIVE我之前有寫相關文章說明

https://isdaniel.github.io/CTE-RECURSIVE/

看更多先前的回應...收起先前的回應...
微笑 iT邦新手 3 級 ‧ 2021-01-22 18:15:18 檢舉

有些 function 是2012以後新增的
請問如果環境限制在SQL Server2008R2以前的版本的話,有辦法嗎?

微笑 iT邦新手 3 級 ‧ 2021-01-22 18:16:24 檢舉

CTE 的寫法好酷,我要花點時間看

看到酷酷的程式碼會覺得對面的人很帥,我一定是病了/images/emoticon/emoticon32.gif

微笑 iT邦新手 3 級 ‧ 2021-01-25 09:34:23 檢舉

後來將SELECT CTE 的方法改寫成

INSERT INTO @TABLE_B ([Start],[End],Qty)
select prefix+REPLICATE('0', paddingOfZero)+cast(fromNum as NVARCHAR),
	   prefix+REPLICATE('0', paddingOfZero)+cast(fromNum as NVARCHAR),1
from CTE

就可以拿到 2008 R2 以前的版本使用了!

故以此解答為最佳,感謝石頭大大回答/images/emoticon/emoticon37.gif

微笑 iT邦新手 3 級 ‧ 2021-01-25 11:57:13 檢舉

index scan失效似乎會是未來效能上的隱憂...如果考慮到此問題或許還是需要轉後端處理/images/emoticon/emoticon33.gif

石頭 iT邦高手 1 級 ‧ 2021-01-25 12:05:50 檢舉

有些 function 是2012以後新增的
請問如果環境限制在SQL Server2008R2以前的版本的話,有辦法嗎?

可以只是寫法上比較麻煩.

石頭 iT邦高手 1 級 ‧ 2021-01-25 12:07:57 檢舉

index scan失效似乎會是未來效能上的隱憂...如果考慮到此問題或許還是需要轉後端處理

如果要在DB就需要調整一下 目前資料表結構並建立合理Index

1
純真的人
iT邦大師 1 級 ‧ 2021-01-22 23:47:40

沒用到T-SQL
你參考看看~

DECLARE @TABLE_A AS TABLE
(
   [Start] varchar(20),
   [End] varchar(20),
   [Qty] numeric(10)
)


INSERT INTO @TABLE_A VALUES('ABC001','ABC005',5)
INSERT INTO @TABLE_A VALUES('ABC007','ABC007',1)
INSERT INTO @TABLE_A VALUES('AC0002','AC0003',2)

select left([Start],3) + right('00'+ Convert(varchar,Convert(int,Right([Start],3)) + n-1),3) [Start]
,left([Start],3) + right('00'+ Convert(varchar,Convert(int,Right([Start],3)) + n-1),3) [End]
,1 [Qty]
from @TABLE_A a
,(
	select Row_Number()Over(order by number) n
	from master..spt_values
) k
where n <= [Qty]

https://ithelp.ithome.com.tw/upload/images/20210122/20061369zkBmWhlNpd.png

看更多先前的回應...收起先前的回應...
微笑 iT邦新手 3 級 ‧ 2021-01-23 00:26:41 檢舉

這寫法預設了前置碼不會大於三碼and流水碼長一定不小於3碼hmm...不過如果有關聯另一table上的資料當變數或許就可行,應該跟 石頭 大大說的是一樣的

嗯~其實還是要看作者實際的資料庫再去調整優化即可~

微笑 iT邦新手 3 級 ‧ 2021-01-25 11:48:59 檢舉

因為資料規則上,容許的長度差異挺大的
使用統一的規則來判斷的話,直覺上如果遇到比較極端的資料容易發生致命的BUG
因此這邊最後採用石頭大大的答案。

但是您的做法想必是更有效率的,若此次是個人專案的話或許就會採用此寫法也說不定,也感謝您的回答喔!/images/emoticon/emoticon41.gif

恩恩~沒問題唷~方便你都可唷~

0
x721221
iT邦新手 5 級 ‧ 2021-05-07 11:13:26

這問題算月經文嗎

看更多先前的回應...收起先前的回應...
微笑 iT邦新手 3 級 ‧ 2021-05-07 11:50:21 檢舉

wwww

微笑 iT邦新手 3 級 ‧ 2021-05-07 11:55:08 檢舉

應該不算

x721221 iT邦新手 5 級 ‧ 2021-05-07 13:17:05 檢舉

我想說類似的問題看到蠻多次的...剛剛去看才發現原來都是不是樓主的文,那可能不算月經文;是我資歷太淺,打擾到你回覆我的問題,不好意思XD

微笑 iT邦新手 3 級 ‧ 2021-05-07 13:33:16 檢舉

都不是同一個人發的文那算月經文吧,一直看到的話

x721221 iT邦新手 5 級 ‧ 2021-05-07 14:04:46 檢舉

我覺得如果之前的答案解決你的問題,才繼續干擾別人發問的話
挺不好意思的XD

x721221 iT邦新手 5 級 ‧ 2021-05-07 14:06:24 檢舉

不過既然你都這樣問一次了
個人深見認為,問題能要亂干擾人問完當然會更好

x721221 iT邦新手 5 級 ‧ 2021-05-07 14:08:37 檢舉

像是之前有人建議你實際的資料庫去調整,或不要亂人別人問題 (雖然有點不太可)
這類的問題如果你當時別點進去的話又留風涼話應該當個觀眾都可以

我要發表回答

立即登入回答