用一句SQL解決SQL中斷號問題
名詞解釋
斷號：比如，連續(xù)生成的編號，由于某種操作(通常為刪除)后，產(chǎn)生不連續(xù)的編號，我們將這種不連續(xù)的編號稱為斷號。
例如，數(shù)據(jù)庫中有一個字段叫合同編號，正常格式為201106_011(表示2011年6月的第11個合同)，那么它前面的一個合同編號應(yīng)該為201106_10，后面的一個應(yīng)該為201106_12，當(dāng)我們刪除了合同201106_011，就會出現(xiàn)201106_010后面直接是201106_012，這種情況下叫做斷號。
傳統(tǒng)系統(tǒng)中，像這種斷號的情況很常見，比如數(shù)據(jù)庫中的列為遞增類型，當(dāng)刪除某行后，就會出現(xiàn)斷號，而經(jīng)常有客戶提出需求，不希望出現(xiàn)斷號的情況。解決方案通常就是，如果刪除了某行數(shù)據(jù)，那么下次新增時，應(yīng)該將斷號補(bǔ)齊。
問題很簡單，解決方法也很簡單：
寫一個C#方法，用來獲取下一條記錄的編號：
- public static int GetNextNumber(int[] iNumList)
- {
- int iTempStr = iNumList[0]; //用一個臨時變量保存上一條記錄的編號
- for (var i = 0; i < iNumList.Length - 1; i++)
- {
- if (i == 0)
- {
- iTempStr = iNumList[i];
- }
- //如果出現(xiàn)斷號，則補(bǔ)齊斷號
- if ((iNumList[i] - iTempStr) > 1)
- {
- return iTempStr + 1;
- }
- else
- {
- iTempStr = iNumList[i];
- }
- continue;
- }
- return iNumList[iNumList.Length - 1] + 1;
- }
當(dāng)然，這段代碼也可以簡寫為以下形式：
- public static int GetNextNumber3(int[] iNumList)
- {
- for (int i = 0, j = 1; j < iNumList.Length - 1; i++, j++)
- {
- //如果出現(xiàn)斷號，則補(bǔ)齊斷號
- if ((iNumList[j] - iNumList[i]) > 1)
- {
- return iNumList[i] + 1;
- }
- }
- return iNumList[iNumList.Length - 1] + 1;
- }
測試代碼如下:
- static void Main(string[] args)
- {
- int[] iNums = { 1, 2, 4, 5, 6, 9, 10 }; //刪除了數(shù)組中的3,7,8，即3,7,8為斷號，下次新增時，希望產(chǎn)生的斷號為3
- System.Console.WriteLine(BreakNumber.GetNextNumber3(iNums));
- System.Console.WriteLine(BreakNumber.GetNextNumber(iNums));
- }
運(yùn)行結(jié)果如下：
前幾天再次接觸到這個問題，由于特殊的場景，再用C#反而會增加開發(fā)難度，如果想法通過SQL來解決問題：
建表及制造數(shù)據(jù)SQL:
- CREATE TABLE testTable
- (
- Code int primary key
- )
- INSERT INTO testTable(Code) VALUES (1)
- INSERT INTO testTable(Code) VALUES (2)
- INSERT INTO testTable(Code) VALUES (3)
- INSERT INTO testTable(Code) VALUES (4)
- INSERT INTO testTable(Code) VALUES (5)
- INSERT INTO testTable(Code) VALUES (6)
- INSERT INTO testTable(Code) VALUES (7)
- INSERT INTO testTable(Code) VALUES (8)
- INSERT INTO testTable(Code) VALUES (9)
- INSERT INTO testTable(Code) VALUES (10)
然后再同樣刪除第3、7、8行的數(shù)據(jù)，使這三行產(chǎn)生斷號：
- DELETE FROM testTable WHERE Code in (3,7,8)
分析：要產(chǎn)生連號，即是要讓Code這一列上連續(xù)的，也就是說每每兩行之間的Code相差為1
由于Code是從1開始的(從其他數(shù)字開始的也是同理計算)，即按Code從小到大排序號，Code為1的行應(yīng)該為第一行，Code為10的行應(yīng)該在第10行，即Code=行號，
既然這樣，預(yù)覽數(shù)據(jù)如下：
刪除數(shù)據(jù)前的排號：
刪除數(shù)據(jù)后的排號：
很明顯發(fā)現(xiàn)，刪除數(shù)據(jù)前，Code=行號，刪除后Code不等于等號，而刪除數(shù)據(jù)后的第一行Code不等于行號的數(shù)據(jù)，即是第一個出現(xiàn)斷號的數(shù)據(jù)，即為我們想要查詢的結(jié)果。
如是，如果數(shù)據(jù)庫中有斷號，則可以用以下語句直接查出斷號：
結(jié)果立現(xiàn)。
這段代碼還存在一個缺陷，即此方法專用來處理有斷號的情況，如果不存在斷號時，應(yīng)該返回Max(RowNumber)+1。正確代碼應(yīng)該如下：
至此，我今天要講的基本結(jié)束，此處借用了SQL2005的方法row_number ，其他數(shù)據(jù)庫中也有類似的方法，大家可以自己摸索。
問題完全解決了嗎?大家可以發(fā)現(xiàn)，以上出現(xiàn)了斷號的情況，都是從小開始補(bǔ)號，比如3,7,8同時為斷號，則補(bǔ)3。假如有客戶要求從大號開始補(bǔ)號(即3,7,8斷號時，補(bǔ)8呢)，怎么處理?
前面兩種通過C#方法操作的就很容易了，這里主要說一下通過SQL處理的方法：
那么再擴(kuò)展一下，如何查出所有的斷號呢?
要實現(xiàn)這個功能，一般想法是將當(dāng)前Code與上一行的Code進(jìn)行對比，但由于可能出現(xiàn)連續(xù)斷號的情況(例如刪除了 Code=7、8、9三行)。此時該如何處理呢?
我的解決方法是，假如max(code)等于100，那么我先構(gòu)造出100行(怎樣構(gòu)造?數(shù)據(jù)庫中隨便找個行數(shù)大于100的表，select top 100就行了，如果沒有行數(shù)大于100的表，就聯(lián)合查詢構(gòu)造出100行吧)，再用這一100行的行號分別和code進(jìn)行對比，如果存在Code<>行號的，即該處為斷裂號，示例如下：
假設(shè)系統(tǒng)中已經(jīng)存在另一張表A，它的總行數(shù)>max(Code)，【注：當(dāng)然，如果不存在這樣的表，也可以通過select 的方式構(gòu)造出來】，
查詢所有斷號的SQL如下：
至此，問題結(jié)束，以上代碼的優(yōu)點(diǎn)在于只用一個SQL語句，而不需要用存儲過程、用戶自定義函數(shù)或C#中的循環(huán)，就可以解決各種斷號問題，當(dāng)然為了性能方面還可以再做優(yōu)化，在此不列出。
原文鏈接：http://www.cnblogs.com/Deper/archive/2011/06/10/2073909.html
【編輯推薦】
