使用 MS SQL
現有一 TABLE_A 欄位資料如下
我希望可以用 T-SQL 將 TABLE_A 資料拆開後存於 TABLE_B
規則特徵:
以上是在實務碰到的需求，後來用後端解決了
但是因為寫法實在有點醜，想請教更好的做法
測試資料提供
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)
已邀請的邦友 {{ invite_list.length }}/5
就如果留言說的我會建議你資料格式調整一下,如果不調整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那邊你沒特別說邏輯 我就針對你提問的流水號寫語法,
關於CTE-RECURSIVE我之前有寫相關文章說明
有些 function 是2012以後新增的
請問如果環境限制在SQL Server2008R2以前的版本的話，有辦法嗎?
CTE 的寫法好酷，我要花點時間看
看到酷酷的程式碼會覺得對面的人很帥，我一定是病了
後來將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 以前的版本使用了!
故以此解答為最佳，感謝石頭大大回答
沒用到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]
這問題算月經文嗎
iThome鐵人賽
看影片追技術