想插入name的时候首先检测需要输入属于哪个组,得到max(ID),然后max(ID)+1,输入name之后,保存。但是由于ID是VARchar类型,我无法实现max(ID)+1.我是新手,大家多多指教。谢谢
ID name
a-001 t
a-002 d
b-001 a
b-002 s
b-003 g
c-001 h
ID name
a-001 t
a-002 d
b-001 a
b-002 s
b-003 g
c-001 h
insert into tb values('a-001' , 't')
insert into tb values('a-002' , 'd')
insert into tb values('b-001' , 'a')
insert into tb values('b-002' , 's')
insert into tb values('b-003' , 'g')
insert into tb values('c-001' , 'h')
godeclare @id as varchar(10)
declare @maxid as varchar(10)
set @id = 'a'
set @maxid = nullset @maxid = (select max(id) from tb where left(id,charindex('-',id)-1) = @id)if @maxid is null
set @maxid = @id + '-001'
else
set @maxid = @id + '-' + right('00'+cast(cast(substring(@maxid , charindex('-',@maxid) + 1 , len(@maxid)) as int) + 1 as varchar),3)print @maxid/*
a-003
*/drop table tb
(
id nvarchar(5),
name nvarchar(50)
)insert tb select 'a-001','t'
insert tb select 'a-002','d'
insert tb select 'b-001','a'
insert tb select 'b-002','s'
insert tb select 'b-003','g'
insert tb select 'c-001','h'
DECLARE @idm NVARCHAR(3),@type NVARCHAR(1)
SELECT @idm = '001',@type = 'a'
WHILE EXISTS (SELECT * FROM tb WHERE right(id,3)=@idm and id like @type+'%')
SELECT @idm=REPLICATE('0',3-LEN(CAST(CAST(@idm AS int)+1 AS varchar)))+CAST(CAST(@idm AS int)+1 AS varchar)
select @type+'-'+@idmdrop table tb-----
a-003