sqlserver用存储过程实现自动取数并编号
- 先建表
create table #x (id varchar(20), user_id varchar(20), rowid int, created varchar(20), posid varchar(20), rowid2 int)
- 代码实现
declare @a int declare @b varchar(2) ='x' declare @c varchar(2) ='s' select @a=isnull(max(rowid),0) from #x where DATEDIFF(day,created,getdate())=0 if object_id('tempdb..#s')is not null drop table #s select top 1 id, user_id, (ROW_NUMBER()over(order by id)+@a) as rowid , GETDATE() as created, (@b+cast((ROW_NUMBER() over(order by id)+@a) as varchar(20))) as posid into #s from sales_rn insert into #x (id,user_id,rowid,created,posid) select *from #s select @a=isnull(max(rowid2),0) from #x where DATEDIFF(day,created,getdate())=0 if object_id('tempdb..#p')is not null drop table #p select top 1 id, user_id, GETDATE() as created, (@c+cast(ROW_NUMBER() over(order by id)+@a as varchar)) as posid, (ROW_NUMBER()over(order by id)+@a) as rowid2 into #p from sales_rn insert into #x (id,user_id,created,posid,rowid2) select *from #p select * from #x