iT邦幫忙

1

SQL 將號碼照順序插入並自動+1

請教各位,我要使用一個迴圈,來把發票號碼按照順序輸入,且發票號碼不能重複所以使用過要自動將數字+1,主要想請問2個語法

1.要如何精確的選擇到最小的單別+單號(單別、單號可能分別重複,但單別+單號一定是唯一值)如下圖取號順序為 1AA-1111 --> 1AA-1112 --> 2AA-1111 --> 3AA-1110
2.要如何讓英文+數字的欄位自動+1,本來想說用SET RIGHT(MB006,8) = RIGHT(MB006,8)+1但是錯誤

https://ithelp.ithome.com.tw/upload/images/20210115/201165016Tlx04BQX5.png

謝謝

min 、max、 concat

先給你這幾個函數。然後再看看你處理的sql。
沒辦法處理的話就再問。
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
0
純真的人
iT邦大師 1 級 ‧ 2021-01-15 14:15:42
最佳解答

我也來練手T-SQL寫程式..@@
引用微笑的前段建立...

--建表
DECLARE @COP AS TABLE
(
   [A001] varchar(4),
   [A002] varchar(5),
   [A003] varchar(15)
)

DECLARE @INV AS TABLE
(
   [B001] varchar(15)
)

--放入參考資料
INSERT INTO @COP VALUES('2AA','1111','')
INSERT INTO @COP VALUES('1AA','1111','')
INSERT INTO @COP VALUES('3AA','1110','')
INSERT INTO @COP VALUES('1AA','1112','')

INSERT INTO @INV VALUES('AB00000000')

declare @i int = 0
declare @j int = 0
declare @k int = 0
declare @n int = 0
declare @Count int
declare @data1 varchar(4)
declare @data2 varchar(5)
declare @Head varchar(2)
declare @Body varchar(8)
declare @Str varchar(10)

select @Count=count(0)
from @COP
where isNull([A003],'') = ''

while(@i<@Count)
begin
	select top 1 @data1=[A001]
	,@data2=[A002]
	from @COP
	where isNull([A003],'') = ''

	while(@j<1)
	begin
		select @Head = left([B001],2)
		,@Body=right([B001],8)
		from @INV
        
		set @n = Convert(int,@Body)
        
		select @k=1
		from @COP
		where [A003] = @Head + @Body

		if(@k=0)
		begin
			set @Str = @Head + @Body
			set @j = 1
            
			update @COP
			set [A003] = @Str
			where [A001]=@data1
			and [A002] = @data2

			set @n = @n + 1
			set @Str = @Head + right('0000000' + Convert(varchar(8), @n),8)

			update @INV
			set [B001] = @Str

		end
		else
		begin
			set @n = @n + 1
			set @Str = @Head + right('0000000' + Convert(varchar(8), @n),8)
			update @INV
			set [B001] = @Str
		end
	end

	set @i = @i + 1
	set @j = 0
end

select *
from @COP

select *
from @INV

https://ithelp.ithome.com.tw/upload/images/20210115/20061369ZyfzaNhcVe.png

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

對喔...獲得數量時就該判斷只抓沒發票的筆數了
偷拿一些去改,不好意思/images/emoticon/emoticon13.gif

微笑 iT邦新手 1 級 ‧ 2021-01-15 15:38:20 檢舉

因為樓主問題有要求希望可以 [按照最小A001跟A002的順序] 輸入發票,所以我有故意把COP表的資料順序打亂

如果我沒理解錯,取樣前應該會需要排序一下/images/emoticon/emoticon37.gif

哈~我是加上防呆~避免曾經已有發票記錄,號碼繼續取下一號0.0a

微笑 iT邦新手 1 級 ‧ 2021-01-15 15:55:11 檢舉

可是發票號碼重複是有可能的,除非樓主的table有年份欄位,可以大約以一兩年內的資料做比對就好

fufujane iT邦新手 5 級 ‧ 2021-01-18 16:08:40 檢舉

感謝大大回覆,目前還在研究各方法改寫套用到主程式裡;實際Table是有日期的~

有日期也可加上防呆之一~

0
微笑
iT邦新手 1 級 ‧ 2021-01-15 12:15:58

方法有點笨,之後有其他大神的話最佳請給他們就好。練手而已。

--建表
DECLARE @COP AS TABLE
(
   [A001] varchar(4),
   [A002] varchar(5),
   [A003] varchar(15)
)

DECLARE @INV AS TABLE
(
   [B001] varchar(15)
)

--放入參考資料
INSERT INTO @COP VALUES('2AA','1111','')
INSERT INTO @COP VALUES('1AA','1111','')
INSERT INTO @COP VALUES('3AA','1110','')
INSERT INTO @COP VALUES('1AA','1112','')

INSERT INTO @INV VALUES('AB00000000')

--宣告變數
DECLARE @_B001 varchar(15)
DECLARE @_COPCount int
DECLARE @_i int

SELECT @_B001 = B001 FROM @INV

SELECT @_COPCount = COUNT(*) FROM @COP WHERE ISNULL(A003,'')=''

SET @_i = 0

--新增資料
WHILE(@_i<@_COPCount)
BEGIN
	WITH TEMPCOP AS(
	SELECT TOP 1 * FROM @COP WHERE ISNULL(A003,'')='' ORDER BY A001,A002
	)
	UPDATE TEMPCOP SET A003=@_B001
	SET @_B001 = SUBSTRING(@_B001,0,3)+RIGHT('00000000'+CAST( CAST( SUBSTRING(@_B001,3, LEN(@_B001)) AS INT) + 1 AS VARCHAR(15)),8)
	UPDATE @INV SET B001=@_B001
	SET @_i = @_i + 1
END

/*顯示結果
@COP
2AA	1111	AB00000002
1AA	1111	AB00000000
3AA	1110	AB00000003
1AA	1112	AB00000001

@COP Order by
1AA	1111	AB00000000
1AA	1112	AB00000001
2AA	1111	AB00000002
3AA	1110	AB00000003

@INV
AB00000004
*/
select * from @COP

select * from @COP Order by A001,A002

select * from @INV
fufujane iT邦新手 5 級 ‧ 2021-01-18 15:53:10 檢舉

感謝大大回覆,目前照您的方式回去修改我原本的程式,結果取到的發票號碼如下:
https://ithelp.ithome.com.tw/upload/images/20210118/20116501s8pwPH9wu0.png

感覺應該是轉成INT的時候前面字母被吃掉了,尚在研究中

微笑 iT邦新手 1 級 ‧ 2021-01-18 16:05:25 檢舉

如果你是用我的範例來做,第一個值(00000001)是直接以原內容寫入的,沒有做分割
猜測你原本的資料與範例不太一樣

如果要調整分割範圍可以使用 純真的人 的範例,他在分割字串的部份使用兩個變數做處理,這樣子比我的範例好修改許多

0
japhenchen
iT邦超人 1 級 ‧ 2021-01-15 12:26:18

................恕刪~~回錯

0
rogeryao
iT邦大師 1 級 ‧ 2021-01-15 17:59:02

1.先備份 COP 及 INV 兩個 TABLE
2.假設 COP 的 A003 未曾使用過 AB 開頭的發票

CREATE TABLE COP (
A001 varchar(40) NULL ,
A002 varchar(40) NULL ,
A003 varchar(40) NULL);

INSERT INTO COP (A001,A002,A003)
VALUES 
('1AA','1111',NULL),
('1AA','1112',''),
('2AA','1111',''),
('2AA','1112','XX55555555'),
('3AA','1110','');
CREATE TABLE INV (
B001 varchar(40) NULL);

INSERT INTO INV (B001)
VALUES 
('AB00000000');
BEGIN TRANSACTION;  
-- 更新 COP 發票號碼 : A003
UPDATE A
SET A.A003=B.A003NEW
FROM COP AS A
LEFT JOIN (
SELECT A001,A002,
(SELECT LEFT(B001,2) FROM INV)+RIGHT('00000000'+CONVERT(VARCHAR(8),
(SELECT CONVERT(INT,SUBSTRING(B001,3,8)) FROM INV)+
CONVERT(VARCHAR(8),ROW_NUMBER() OVER (ORDER BY A001,A002)-1)),8) AS A003NEW
FROM COP
WHERE ISNULL(A003,'')=''
) AS B ON B.A001=A.A001 AND B.A002=A.A002
WHERE ISNULL(A.A003,'')='';

-- 更新 INV 已用發票號碼 : B001
UPDATE C
SET C.B001=(SELECT
LEFT(MAX(A003),2)+
RIGHT('00000000'+CONVERT(VARCHAR(8),CONVERT(INT,SUBSTRING(MAX(A003),3,8))+1),8) 
FROM COP
WHERE LEFT(A003,2)=LEFT(C.B001,2))
FROM INV AS C;

COMMIT; 

Demo

fufujane iT邦新手 5 級 ‧ 2021-01-18 16:05:11 檢舉

感謝回覆,不好意思小弟愚昧,想請問更新發票號碼的後方這個步驟大概是為何呀
WHERE ISNULL(A003,'')='' ) AS B ON B.A001=A.A001 AND B.A002=A.A002

rogeryao iT邦大師 1 級 ‧ 2021-01-18 16:32:16 檢舉

請參閱 : 子查詢及簡要說明

-- 只對 COP A003 是空的或是 null 準備填入"試算發票號碼"
==> 可先看到即將被異動的資料
rogeryao iT邦大師 1 級 ‧ 2021-01-18 16:54:05 檢舉

若"實際Table是有日期的~"指的是 COP Table,
那麼就必須把條件加在 WHERE ISNULL(A003,'')='' 下面,
共有兩處

另外,假若日期欄位是 Axxx,那麼原本的 ORDER BY A001,A002
應該要改成 ORDER BY Axxx,A001,A002
也就是先依照日期排序在依單別、單號排序,
日期在前面的單據應該填入較小的發票號碼

我要發表回答

立即登入回答