create table ta(id varchar(10)primary key ,name varchar(10)) --用触发器完成 create trigger test_ta_tr on ta instead of insert as declare @int varchar(50) select * into # from inserted select @int=isnull(max(replace(id,'lik_','')),122) from ta--这里改为122就行了 update # set id='lik_'+@int,@int=@int+1 insert ta select * from #--测试 insert ta select 1,'a'union all--这里1为任何值都不影响id插入的值 select 1,'b'select * from taid name ---------- ---------- lik_123 a
用计算列加自增列实现 CREATE TABLE [dbo].[autobill] ( [id] [int] IDENTITY (1, 1) NOT NULL , [bill_no] AS (right(('000000' + rtrim([id])),6)) )
create table tb(id int identity(1,1), b as left('000000',6-len(id))+cast(id as varchar),c varchar(10)) insert into tb(c) select 'a' union all select 'b' union all select 'c' select * from tb go drop ta /* id b c ----------- ------------------------------------ ---------- 1 000001 a 2 000002 b 3 000003 c(3 行受影响) */
快下班了,给你发个以前写的类似的参考吧~ Create procedure pro_saleID(@CustomerId int,@Seller varchar(10),@ItemId int,@Acount int) as declare @saleId varchar(7) declare @maxId int if (select count(*) from Sales_main)=0 begin select @saleId='XSD'+'0001' end else begin select @maxid=max(convert(int,right(SaleId,4))) from Sales_main select @saleId='XSD'+right('0000'+(convert(varchar(4),@maxId+1)),4) end insert Sales_main select @saleId,@CustomerId,@Seller,convert(varchar(10),getdate(),120) insert Sales_sub select @saleId,@ItemId,@AcountGO
create procedure prc_get_cno @cno char(6) output as begin select @cno=max(right(cno,6)) from TableA if @cno is null begin set @cno='000001' end else begin set @cno= case when @cno>0 and @cno<9 then '00000'+convert(char,@cno+1) when @cno>=9 and @cno<99 then '0000'+convert(char,@cno+1) when @cno>=99 and @cno<999 then '000'+convert(char,@cno+1) when @cno>=999 and @cno<9999 then '00'+convert(char,@cno+1) when @cno>=9999 and @cno<99999 then '0'+convert(char,@cno+1) when @cno>=99999 then convert(char,@cno+1) end end end go
--用触发器完成
create trigger test_ta_tr on ta
instead of insert
as
declare @int varchar(50)
select * into # from inserted
select @int=isnull(max(replace(id,'lik_','')),122) from ta--这里改为122就行了
update # set id='lik_'+@int,@int=@int+1
insert ta
select * from #--测试
insert ta
select 1,'a'union all--这里1为任何值都不影响id插入的值
select 1,'b'select * from taid name
---------- ----------
lik_123 a
CREATE TABLE [dbo].[autobill] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[bill_no] AS (right(('000000' + rtrim([id])),6))
)
insert into tb(c)
select 'a'
union all select 'b'
union all select 'c'
select * from tb
go
drop ta
/*
id b c
----------- ------------------------------------ ----------
1 000001 a
2 000002 b
3 000003 c(3 行受影响)
*/
Create procedure pro_saleID(@CustomerId int,@Seller varchar(10),@ItemId int,@Acount int)
as
declare @saleId varchar(7)
declare @maxId int
if (select count(*) from Sales_main)=0
begin
select @saleId='XSD'+'0001'
end
else
begin
select @maxid=max(convert(int,right(SaleId,4))) from Sales_main
select @saleId='XSD'+right('0000'+(convert(varchar(4),@maxId+1)),4)
end
insert Sales_main
select @saleId,@CustomerId,@Seller,convert(varchar(10),getdate(),120)
insert Sales_sub
select @saleId,@ItemId,@AcountGO
as
begin
select @cno=max(right(cno,6)) from TableA
if @cno is null
begin
set @cno='000001'
end
else
begin
set @cno=
case
when @cno>0 and @cno<9 then '00000'+convert(char,@cno+1)
when @cno>=9 and @cno<99 then '0000'+convert(char,@cno+1)
when @cno>=99 and @cno<999 then '000'+convert(char,@cno+1)
when @cno>=999 and @cno<9999 then '00'+convert(char,@cno+1)
when @cno>=9999 and @cno<99999 then '0'+convert(char,@cno+1)
when @cno>=99999 then convert(char,@cno+1)
end
end
end
go