根据年月日生成编号sqlserver

来源：互联网发布：九宫格锁屏软件大全编辑：程序博客网时间：2024/06/03 23:50

create table test(
iid varchar(32) primary key
)
insert into test values('2011120701')

insert into test values('2011120702')
insert into test values('2011120703')
insert into test values('2011120704')

insert into test values('2011120705')
insert into test values('2011120706')
insert into test values('2011120707')
insert into test values('2011120708')
insert into test values('2011120709')

insert into test values('20111207010')
insert into test values('201112070100')

select case
when max(Convert(decimal,iid)) is null then REPLACE(CONVERT(varchar(100), GETDATE(),23),'-','')+'01'
else
REPLACE(CONVERT(varchar(100), GETDATE(),23),'-','')+'0'
+
Convert(varchar(100),Convert(int,right(convert(varchar(32),max(Convert(decimal,iid))),len(convert(varchar(32),max(Convert(decimal,iid))))-len(REPLACE(CONVERT(varchar(100), GETDATE(),23),'-',''))-1))+1)

end as randIid from test

--SELECT convert(int,REPLACE(CONVERT(varchar(100), GETDATE(),23),'-',''))
--SELECT REPLACE('SQSERVER','ER','AA') AS 替换结果