首先解释一下这个表数据的来源:
如:
表A 类型ID;UserID;起始编号;截至编号
通过函数生成一条条的编号表B
表B:类型ID;UserID;编号,状态(初始值为New)现在需求就是,根据表B的这些 类型相同,UserID相同,状态为New的连续号
取出 每组最大号和最小号,也就是每组的首尾编号。我贴几个表A的测试数据
起始编号 截至编号
0201133477e 0201133500e
00102901 00103000
95561e9003613015企 95561e9003613028企
AA29150151 AA29150175
0007751 0013500
HDE-30109740 HDE-30109741
为了方便你们测试,我把生成编号的表函数贴出来
另外弱弱的问一句,我刚发了一条这样的帖子,没了,150呢,能把分还给我吗
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: wzq
-- Create date: 2013/04/12
-- Description:
-- =============================================
drop function fin.GetTabByStr
go
create function fin.GetTabByStr
(
@pStr1 nvarchar(60),
@pStr2 nvarchar(60)
)
returns @tb table
(
strNo nvarchar(60)
)
AS
begin
if(@pStr1 = @pStr2)
begin
insert into @tb values(@pStr1);
end
else
begin
declare @i int;
set @i = 1;
declare @LenStr int; declare @firstDiffPlace int;
declare @firstStr int;
set @firstStr = 0; set @lenStr = len(@pStr1); --1.取出两个字符串第一个不相同的字符串的索引位置。
while(@i <= @lenStr)
begin
declare @str1 nvarchar(1);
declare @str2 nvarchar(1); set @str1 = substring(@pStr1,@i,1);
set @str2 = substring(@pStr2,@i,1);
if(@str1 = @str2)
begin
set @i = @i +1;
end
else
begin
set @firstDiffPlace = @i;
break;
end
end
declare @str nvarchar(60); set @str = substring(@pStr1,@firstDiffPlace,@lenStr); declare @strNo nvarchar(60); declare @chxStr1 nvarchar(60);
declare @chxStr2 nvarchar(60);
declare @strNum1 int;
declare @strNum2 int;
--2判断该字符串是否位数字
if(isnumeric(@str) = 1)
begin
set @chxStr1 = substring(@pStr1,@firstDiffPlace,@lenStr);
set @chxStr2 = substring(@pStr2,@firstDiffPlace,@lenStr); set @strNum1 = convert(int,@chxStr1);
set @strNum2 = convert(int,@chxStr2); while(@strNum1<=@strNum2)
begin
--判断两个截取后转换成数字类型的长度是否相同
if(len(@strNum1) <> len(@strNum2))
begin
set @strNo = substring(@pStr1,1,@firstDiffPlace -1) + replicate('0',len(@strNum2) - len(@strNum1)) + convert(nvarchar(60),@strNum1);
end
if(len(@strNum1) = len(@strNum2))
begin
set @strNo = substring(@pStr1,1,@firstDiffPlace -1) + convert(nvarchar(60),@strNum1);
end
insert into @tb values(@strNo);
set @strNum1 = @strNum1 + 1;
end end
else
begin
--3.获取第一个不是数字的字符位置
declare @n int;
set @n = 1;
while(@n <= len(@str))
begin
if(isnumeric(substring(@str,@n,1)) = 0)
begin
set @firstStr = @n;
break;
end
else
begin
set @n = @n + 1;
end
end
set @chxStr1 = substring(@pStr1,@firstDiffPlace,@firstStr -1);
set @chxStr2 = substring(@pStr2,@firstDiffPlace,@firstStr -1); set @strNum1 = convert(int,@chxStr1);
set @strNum2 = convert(int,@chxStr2);
while(@strNum1<=@strNum2)
begin
--判断两个截取后转换成数字类型的长度是否相同
if(len(@strNum1) <> len(@strNum2))
begin
set @strNo = substring(@pStr1,1,@firstDiffPlace -1) + replicate('0',len(@strNum2) - len(@strNum1)) + convert(nvarchar(60),@strNum1) + isnull(substring(@pStr1,@firstDiffPlace+ @firstStr -1,@lenStr),'');
end
if(len(@strNum1) = len(@strNum2))
begin
set @strNo = substring(@pStr1,1,@firstDiffPlace -1) + convert(nvarchar(60),@strNum1) + isnull(substring(@pStr1,@firstDiffPlace+ @firstStr -1,@lenStr),'');
end
insert into @tb values(@strNo);
set @strNum1 = @strNum1 + 1;
end
end
end returnend
把所有非数字的或者第一位是数字的
过滤出来到一列算一个标识例如
0201133477e
0201133500e
这类的type 字段值 就为 0e 剩下的列数字 201133477 和 201133500
95561e9003613015企 95561e9003613028企
这类的type 字段值 e企 数字列955619003613015然后在取最大 最小值。