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

2010-01-06 09:12:33 562 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

金证股份软件测试笔试题面试题 boss上这家公司的HR找到了我，我看了一下这家公司的业务，是我以前做过的基金与证券。还是很感兴趣的，决定去面试看看情况。（因为只是想去涨面试经验，没有想拿offer，一些回答有点不太对，为后面木有offer埋下伏笔。其实有点亏，毕竟走路过去，而且还花了3个小时）地点在金证股份科技大楼8楼。 2月25号主要分为笔试题和面试。 1、笔试题进来的时候直接给我一份笔试题做，做了一个半小时吧。分为3道选择题和1道SQL查询题、1道案例设计题选择题都是考察SQL函数，可能日常生活中没有见过阅读详情

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

...全文

APB Timer验证环境搭建——学习笔记（6）（1）为RELOAD VALUE配置初始值32'h8000_0000，设定边界值32'h4000_0000，可以看到减到想要的边界值的时候，counter重新加载RELOAD VALUE，重新开始计数；这根线，统计等待到中断的次数，与理论值做。（2）设定Reload value。（1）由于counter。recount总共次数；阅读详情

wang_quan_li

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

dawugui 2010-01-06

打赏
举报

[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 行）

*/

wang_quan_li 2010-01-06

打赏
举报

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

dawugui 2010-01-06

打赏
举报

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

--没有数据的时候.

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 行）

*/

dawugui 2010-01-06

打赏
举报

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

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

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

相关推荐