用一句SQL解决SQL中断号问题

名词解释

　　断号：比如，连续生成的编号，由于某种操作（通常为删除）后，产生不连续的编号，我们将这种不连续的编号称为断号。

　　例如，数据库中有一个字段叫合同编号，正常格式为201106_011（表示2011年6月的第11个合同），那么它前面的一个合同编号应该为201106_10，后面的一个应该为201106_12，当我们删除了合同201106_011，就会出现201106_010后面直接是201106_012，这种情况下叫做断号。

　　传统系统中，像这种断号的情况很常见，比如数据库中的列为递增类型，当删除某行后，就会出现断号，而经常有客户提出需求，不希望出现断号的情况。解决方案通常就是，如果删除了某行数据，那么下次新增时，应该将断号补齐。

      问题很简单，解决方法也很简单：

      写一个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];
}
// 如果出现断号，则补齐断号
if ((iNumList[i] - iTempStr) > 1 )
{
return iTempStr + 1 ;
}
else
{
iTempStr = iNumList[i];
}
continue ;
}
return iNumList[iNumList.Length - 1 ] + 1 ;
}

　　 当然，这段代码也可以简写为以下形式：

public static int GetNextNumber3( int [] iNumList)
{
for ( int i = 0 , j = 1 ; j < iNumList.Length - 1 ; i ++ , j ++ )
{
// 如果出现断号，则补齐断号
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 }; // 删除了数组中的3,7,8，即3,7,8为断号，下次新增时，希望产生的断号为3
System.Console.WriteLine(BreakNumber.GetNextNumber3(iNums));
System.Console.WriteLine(BreakNumber.GetNextNumber(iNums));

}

运行结果如下：

　　

　

前几天再次接触到这个问题，由于特殊的场景，再用C#反而会增加开发难度，如果想法通过SQL来解决问题：

建表及制造数据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行的数据，使这三行产生断号：

DELETE FROM testTable WHERE Code in ( 3 , 7 , 8 )

分析：要产生连号，即是要让Code这一列上连续的，也就是说每每两行之间的Code相差为1

由于Code是从1开始的（从其他数字开始的也是同理计算），即按Code从小到大排序号，Code为1的行应该为第一行，Code为10的行应该在第10行，即Code=行号，

既然这样，预览数据如下：

　　删除数据前的排号：

　　

 

　　删除数据后的排号：

　　很明显发现，删除数据前，Code=行号，删除后Code不等于等号，而删除数据后的第一行Code不等于行号的数据，即是第一个出现断号的数据，即为我们想要查询的结果。

　　如是，如果数据库中有断号，则可以用以下语句直接查出断号：

　　

　　结果立现。

　　这段代码还存在一个缺陷，即此方法专用来处理有断号的情况，如果不存在断号时，应该返回Max(RowNumber)+1。正确代码应该如下：

 

　　至此，我今天要讲的基本结束，此处借用了SQL2005的方法row_number ，其他数据库中也有类似的方法，大家可以自己摸索。

　　问题完全解决了吗？大家可以发现，以上出现了断号的情况，都是从小开始补号，比如3,7,8同时为断号，则补3。假如有客户要求从大号开始补号（即3,7,8断号时，补8呢），怎么处理？

　　前面两种通过C#方法操作的就很容易了，这里主要说一下通过SQL处理的方法：

　　

　　那么再扩展一下，如何查出所有的断号呢？

　　要实现这个功能，一般想法是将当前Code与上一行的Code进行对比，但由于可能出现连续断号的情况（例如删除了 Code=7、8、9三行）。此时该如何处理呢？

　　我的解决方法是，假如max(code)等于100，那么我先构造出100行(怎样构造？数据库中随便找个行数大于100的表，select top 100就行了，如果没有行数大于100的表，就联合查询构造出100行吧），再用这一100行的行号分别和code进行对比，如果存在Code<>行号的，即该处为断裂号，示例如下：

假设系统中已经存在另一张表A，它的总行数>max(Code)，【注：当然，如果不存在这样的表，也可以通过select 的方式构造出来】，

查询所有断号的SQL如下：

　　

　　

　　至此，问题结束，以上代码的优点在于只用一个SQL语句，而不需要用存储过程、用户自定义函数或C#中的循环，就可以解决各种断号问题，当然为了性能方面还可以再做优化，在此不列出。   

转载于:https://www.cnblogs.com/Deper/archive/2011/06/10/2073909.html