[SQL Server] 流水號 補號及間斷的問題

程式上遇到需要人工產生流水號,然而在編號有限而沒有辦法無限新增的情況下
這時候就會希望能夠做到補號的動作

ex
假設今天編碼規定從0~9,就只給使用者最多擁有10筆資料

TableA

水果	流水號
西瓜	0
፧
香蕉	6
蘋果	8

由上表可以看出目前 總共有流水號 0,1,2,3,4,5,6,8 這八筆資料
今天如果希望使用者產生資料先補上 編號7 而不是 編號9 的話

首先要想辦法篩選出最小漏號

目前數列為 0,1,2,3,4,5,6,8
將數列+1    1,2,3,4,5,6,7,9
這邊可以注意到,只要將上下去做比對,可以發現上面沒有,而下面有的數字 即是漏號
所以抓最小號碼可以這樣寫

Select Min(流水號+1) As '最小漏號' From TableA Where (流水號+1) Not In (Select 流水號 From TableA)

結果

最小漏號

7

接下來只要把Select出的資料寫進去可以了
這邊分段寫比較容易看

StrTempA= Select Min(流水號+1) As '最小漏號' From TableA 
Where (流水號+1) Not In (Select 流水號 From TableA)

Insert Into TableA (水果, 流水號) Values ('鳳梨',(StrTempA))

結果
TableA

水果	流水號
西瓜	0
፧
香蕉	6
鳳梨	7
蘋果	8

-----------------------------------------------------------------------------------

如果今天編碼規則再多一點變化

TableB

水果	產地	產地碼	流水號
保留A1	ㆍ	A	1
保留B1	ㆍ	B	1
保留B5	ㆍ	B	5
香蕉	台灣	A	6
香蕉	泰國	B	7
蘋果	台灣	A	9

假設 流水號 前面多一碼為產地識別碼,而可以編碼範圍為 A6~A10, B6~B10 各5碼
(A1~A5, B1~B5 為系統保留碼不可使用)

這樣的話一樣要搜尋出最小號碼,不過要排除掉 1~5 號的缺號,並且要分組尋找

那麼首先用原本的程式碼去做篩選,但這邊因為有兩組,所以 Where 的條件要多加子查詢結構的串接:

Select 產地碼, MIN(流水號+1) As '最小漏號' From TableB As TempB
Where (流水號+1) Not In (Select 流水號 From TableB Where 產地碼=TempB.產地碼)
Group By 產地碼

產地碼	流水號
A	2
B	2

這邊看到以原來的方式去寫 會造成篩選到系統碼
---------------------------------------------------------------------------------------
所以我們這邊得來分段處理問題
1.首先要先判斷出是否存在可用的最小碼

條件1: Select 產地碼 From TableB Where 流水號=6

產地碼

A

這樣就可以知道只有產地碼 A 有最小碼,而 B 要從 B6開始補碼

因為待會要把語法拼裝起來,先用子查詢結構來串接待會要放在最外面的母表 B
所以先加上這個條件 (And 產地碼=B.產地碼)

所以改寫成
條件1: Select 產地碼 From TableB Where 流水號=6 And 產地碼=B.產地碼
結果1: 6

而可以推論當 不符合'條件1'-> 結果1, '其他狀況'-> 結果2

那麼 '其他狀況' 下的 結果2 要該如何寫呢? 只要在原本的語句多加上流水號>=6 來排除抓取系統碼的狀況就可以了
結果2:Select 產地碼, MIN(流水號+1) As '最小漏號' From TableB As TempB
Where 流水號>=6 And (流水號+1) Not In (Select 流水號 From TableB Where 產地碼=TempB.產地碼) Group By 產地碼

這邊一樣待會要拼裝語法,所以這邊也用子查詢結構來串接,而原本串接部分也改成串到最外面的母表B,並且只篩選出最小漏號部分
改寫成如下
結果2:Select MIN(流水號+1) As '最小漏號' From TableB
Where 流水號>=6 And (流水號+1) Not In (Select 流水號 From TableB Where 產地碼=B.產地碼) And 產地碼=B.產地碼

再來用(Case When)就可以拼裝成要的結果

Select 產地碼, (Case When Not Exists (條件1) Then 6 Else (結果2) End) As '最小漏號' From TableB As B Group By 產地碼
當中 '條件1' , '結果2'  替換成上面組好的語法就大功造成

結果

產地碼	最小漏號
A	7
B	6

最後再搭配 INSERT INTO SELECT 就可以達到分組補號的效果
---------------------------------------------------------------------------------------