create table test(ID varchar(40))
insert into test values('02')
insert into test values('0201')
insert into test values('03')
insert into test values('05')
gocreate function f_getChild(@ID varchar(40))
returns varchar(40)
as
begin
declare @CID varchar(40)
set @ID=isnull(@ID,'')
if not exists(select 1 from test where ID=@ID+'01')
set @CID=@ID+'01'
else
begin
select
@CID=min(@ID+right('0'+rtrim(right(t.ID,2)+1),2))
from
test t
where
left(t.ID,len(t.ID)-2)=@ID
and
exists(select 1 from test where left(ID,len(ID)-2)=@ID and ID>t.ID)
and
not exists(select 1 from test where ID=@ID+right('0'+rtrim(right(t.ID,2)+1),2))
end
if @CID is null
select @CID=@ID+right('0'+rtrim(right(max(ID),2)+1),2)
from
test
where
left(ID,len(ID)-2)=@ID
return @CID
end
goselect dbo.f_getChild(null)
/*
----------------------------------------
01
*/
select dbo.f_getChild('02')
/*
----------------------------------------
0202
*/
select dbo.f_getChild('0201')
/*
----------------------------------------
020101
*/
select dbo.f_getChild('03')
/*
----------------------------------------
0301
*/insert into test select dbo.f_getChild(null)select dbo.f_getChild(null)
/*
----------------------------------------
04
*/insert into test select dbo.f_getChild(null)select dbo.f_getChild(null)
/*
----------------------------------------
06
*/
godrop function f_getChild
drop table test
go
insert into test values('02')
insert into test values('0201')
insert into test values('03')
insert into test values('05')
gocreate function f_getChild(@ID varchar(40))
returns varchar(40)
as
begin
declare @CID varchar(40)
set @ID=isnull(@ID,'')
if not exists(select 1 from test where ID=@ID+'01')
set @CID=@ID+'01'
else
begin
select
@CID=min(@ID+right('0'+rtrim(right(t.ID,2)+1),2))
from
test t
where
left(t.ID,len(t.ID)-2)=@ID
and
exists(select 1 from test where left(ID,len(ID)-2)=@ID and ID>t.ID)
and
not exists(select 1 from test where ID=@ID+right('0'+rtrim(right(t.ID,2)+1),2))
end
if @CID is null
select @CID=@ID+right('0'+rtrim(right(max(ID),2)+1),2)
from
test
where
left(ID,len(ID)-2)=@ID
return @CID
end
goselect dbo.f_getChild(null)
/*
----------------------------------------
01
*/
select dbo.f_getChild('02')
/*
----------------------------------------
0202
*/
select dbo.f_getChild('0201')
/*
----------------------------------------
020101
*/
select dbo.f_getChild('03')
/*
----------------------------------------
0301
*/insert into test select dbo.f_getChild(null)select dbo.f_getChild(null)
/*
----------------------------------------
04
*/insert into test select dbo.f_getChild(null)select dbo.f_getChild(null)
/*
----------------------------------------
06
*/
godrop function f_getChild
drop table test
go
2、中间断号,必须要找出断号对数据进行补齐
3、序列完整,只需末尾追加以上用户定义函数就是按照这样的思路来处理的。
create function f_getChild(@ID varchar(40))
returns varchar(40)
as
begin
declare @CID varchar(40)
set @ID=isnull(@ID, ' ') -- 首位断号的处理逻辑
if not exists(select 1 from test where ID=@ID+ '01 ')
set @CID=@ID+ '01 '
-- 中间断号的处理逻辑
else
begin
select
@CID=min(@ID+right( '0 '+rtrim(right(t.ID,2)+1),2))
from
test t
where
left(t.ID,len(t.ID)-2)=@ID
and
exists(select 1 from test where left(ID,len(ID)-2)=@ID and ID >t.ID)
and
not exists(select 1 from test where ID=@ID+right( '0 '+rtrim(right(t.ID,2)+1),2))
end
-- 序列完整的处理逻辑
if @CID is null
select @CID=@ID+right( '0 '+rtrim(right(max(ID),2)+1),2) from test where left(ID,len(ID)-2)=@ID
return @CID
end
go