做个总结:
declare @t table(code varchar(6))
insert into @t select 'A001'
insert into @t select 'A001'
insert into @t select 'A003'
insert into @t select 'B001'
insert into @t select 'B002'
insert into @t select 'D003'
declare @s table(code varchar(6))
insert into @s
select 'A' union all
select 'B' union all
select 'C' union all
select 'D' union all
select 'F'
select
a.m+right(rtrim(min(a.n)+1001),3) as newCode
from
(select left(code,1) as m,cast(right(code,3) as int) n from @t
union
select code,0 from @s) a LEFT JOIN @t B on left(B.code,1)=a.m and right(B.code,3)=a.n+1
where B.code is null
group by
a.m
------函数------
drop function dbo.numToStr
go
create function dbo.numToStr(@num int)
returns char(3)
begin
declare @str char(3)
declare @sNum varchar(3)
set @sNum=cast(@num as varchar)
if len(@sNum)=1
set @str = '00'+@sNum
else if len(@sNum)=2
set @str = '0'+@sNum
else
set @str = @sNum
return @str
end
go
drop function dbo.strToNum
go
create function dbo.strToNum(@str char(3))
returns int
begin
declare @num int
if substring(@str,1,2)='00'
set @num=cast(substring(@str,3,1) as int)
else if substring(@str,1,1)='0'
set @num=cast(substring(@str,2,2) as int)
else
set @num=cast(@str as int)
return @num
end
go
------函数------
select
case
when min(dbo.strToNum(substring(id,2,3)))<>1 then (select left(id,1)+'001' from konghao as c where left(c.id,1)=left(konghao.id,1))
else
(select top 1 left(a.id,1)+dbo.numToStr((dbo.strToNum(substring(a.id,2,3))+1)) from konghao as a where left(a.id,1)=left(konghao.id,1) and not exists(select 1 from konghao as b where left(b.id,1)=left(a.id,1) and b.id=left(b.id,1)+dbo.numToStr(dbo.strToNum(substring(a.id,2,3))+1)))
end
as maxUnUsedID
正确的解法是:
1. 首先得有一个专门的表指出你所有的类名,因为这个题目要求未出现的项,即集合的补集,那就先得给出全集。例如是A B C D, 还是A B C D E F, 或者根本没有规律如A E G T X Y。假设这个表名是CLASSNAME。
2. 想办法把你的三种情况变成一种,这样就可用一条SQL语句来产生输出。办法很简单,在你的原数据中加入所有类的000纪录,实际上,这是一种修改边界条件的方法,这样一来,只有一种情况了,那就是查找最小的未使用编号了。语句如下(假设你的数据表名是CLASSDATA)
select left(code,1) as m,cast(right(code,3) as int) n from CLASSDATA
union
select CODE,0 from CLASSNAME
3. 对上面语句产生的表做查询,找出每类中后面没有连续值的所有编号中最小的那个,具体写法可参见libin_ftsafe(子陌红尘)回帖的语句。
declare @mPID char(4),
@mFilter char(2),
@nTmpID int
set @mFilter = @inStr+'%'
select ID=Identity(int,1,1),a.* into #tmp
from (select distinct Col from t1 where COl like+@mFilter)a
if exists(select top 1 * from #tmp)
begin
select top 1 @nTmpID=ID from #tmp where ID<>Convert(int,right(Col,3))
if @@rowCount=0
select @nTmpID=max(ID)+1 from #tmp
if @nTmpID<10
set @mPID = @inStr+'00'+Convert(Char(1),@nTmpID)
else if @nTmpID<100
set @mPID = @inStr+'0'+Convert(Char(1),@nTmpID)
else if @nTmpID<1000
set @mPID = @inStr+Convert(Char(1),@nTmpID)
select @c = 'A'
if not exists(select 1 from tb where [id]=@c+'001')
begin
select @cID=@c+'001'
end
else
begin
select @cID= min([id]) from tb where left([id],1)=@c and cast(right([id],3) as int)+1 not in (select cast(right([id],3) as int) from tb where left([id],1)=@c)
select @cID = @c + right('000'+ltrim(rtrim(cast(cast(right(@cID,3) as int)+1 as char(3)))) ,3)
end