根据年月日生成编号sqlserver

最新推荐文章于 2022-06-27 16:53:12 发布

menggucaoyuanliang

最新推荐文章于 2022-06-27 16:53:12 发布

阅读量670

点赞数

分类专栏： sql server 文章标签： sqlserver insert table null

本文链接：https://blog.csdn.net/menggucaoyuanliang/article/details/7051267

版权

sql server 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

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 替换结果