编号连续不能断号，断号后补号

[wang_quan_li]

编号分2类，一是2009JXX0001，二是2009CXX0002
XX是人员编号，如：01，02...
0001、0002、0003等大流水号是所有人的流水号，不是每个人的流水号，
怎么实现编号连续不能断号，断号后补号
用C实现最佳。

[dawugui]

http://topic.csdn.net/u/20100105/22/ddc00e54-0a31-43d3-9853-7bcf49160301.html?81405

这个帖要的是整型,你自己改为字符串型即可.

[dawugui]

以下代码一样,只是测试数据不同.

--没有数据的时候.

create table tb(col varchar(20))
go

create proc my_proc 
as
begin
  declare @col as varchar(20)
  if not exists(select 1 from tb)
     select '0001'
  else
     begin
       select @col = right('000' + cast(cast(min(right(m.col,4)) as int) + 1 as varchar),4) from
       (select t.* , px = (select count(1) from tb where right(col,4) < right(t.col,4)) + 1 from tb t) m,
       (select t.* , px = (select count(1) from tb where right(col,4) < right(t.col,4)) + 1 from tb t) n
       where m.px = n.px - 1 and cast(right(m.col,4) as int) <> cast(right(n.col,4) as int) - 1
       if @col is not null
          select @col
       else
       begin
         select @col = right('000' + cast(cast(max(right(col,4)) as int) + 1 as varchar),4) from tb
         select @col
       end
     end
end
go

exec my_proc

drop table tb
drop proc my_proc

/*
     
---- 
0001

（所影响的行数为 1 行）
*/

--存在有断号的时候.

create table tb(col varchar(20))
insert into tb values('2009JXX0001')
insert into tb values('2009CXX0003')
go

create proc my_proc 
as
begin
  declare @col as varchar(20)
  if not exists(select 1 from tb)
     select '0001'
  else
     begin
       select @col = right('000' + cast(cast(min(right(m.col,4)) as int) + 1 as varchar),4) from
       (select t.* , px = (select count(1) from tb where right(col,4) < right(t.col,4)) + 1 from tb t) m,
       (select t.* , px = (select count(1) from tb where right(col,4) < right(t.col,4)) + 1 from tb t) n
       where m.px = n.px - 1 and cast(right(m.col,4) as int) <> cast(right(n.col,4) as int) - 1
       if @col is not null
          select @col
       else
       begin
         select @col = right('000' + cast(cast(max(right(col,4)) as int) + 1 as varchar),4) from tb
         select @col
       end
     end
end
go

exec my_proc

drop table tb
drop proc my_proc

/*
     
---- 
0002

（所影响的行数为 1 行）
*/

--不存在断号时.

create table tb(col varchar(20))
insert into tb values('2009JXX0001')
insert into tb values('2009CXX0002')
go

create proc my_proc 
as
begin
  declare @col as varchar(20)
  if not exists(select 1 from tb)
     select '0001'
  else
     begin
       select @col = right('000' + cast(cast(min(right(m.col,4)) as int) + 1 as varchar),4) from
       (select t.* , px = (select count(1) from tb where right(col,4) < right(t.col,4)) + 1 from tb t) m,
       (select t.* , px = (select count(1) from tb where right(col,4) < right(t.col,4)) + 1 from tb t) n
       where m.px = n.px - 1 and cast(right(m.col,4) as int) <> cast(right(n.col,4) as int) - 1
       if @col is not null
          select @col
       else
       begin
         select @col = right('000' + cast(cast(max(right(col,4)) as int) + 1 as varchar),4) from tb
         select @col
       end
     end
end
go

exec my_proc

drop table tb
drop proc my_proc

/*
     
---- 
0003

（所影响的行数为 1 行）
*/

[wang_quan_li]

年度没有考虑进去
我试了到2010年编号不从0001开始

[dawugui]

[Quote=引用 3 楼 wang_quan_li 的回复:]
年度没有考虑进去
我试了到2010年编号不从0001开始
[/Quote]
你自己再把条件加上即可.
例如:

create table tb(col varchar(20)) 
go

create proc my_proc @year varchar(4)
as
begin
  declare @col as varchar(20)
  if not exists(select 1 from tb where left(col,4) = @year)
     select '0001'
  else
     begin
       select @col = right('000' + cast(cast(min(right(m.col,4)) as int) + 1 as varchar),4) from
       (select t.* , px = (select count(1) from tb where left(col,4) = @year and right(col,4) < right(t.col,4)) + 1 from tb t where left(col,4) = @year) m,
       (select t.* , px = (select count(1) from tb where left(col,4) = @year and right(col,4) < right(t.col,4)) + 1 from tb t where left(col,4) = @year) n
       where m.px = n.px - 1 and cast(right(m.col,4) as int) <> cast(right(n.col,4) as int) - 1
       if @col is not null
          select @col
       else
       begin
         select @col = right('000' + cast(cast(max(right(col,4)) as int) + 1 as varchar),4) from tb where left(col,4) = @year
         select @col
       end
     end
end
go

exec my_proc '2010'

drop table tb
drop proc my_proc

/*
---- 
0001

（所影响的行数为 1 行）
*/