那只能用触发器解决了:1、增加一个自增的整形列,如 ID identity (1, 1) 2、编写一个触发器 for insert update 表 set 自编号列 = 'JK' + cast([ID] as varchar(20)) where [ID] in (select [ID] from inserted)
可以加触发器 计算当前最后一条记录的id,然后+1,但效率有点问题.如果JK字母前缀是固定的,建议生成int型identity自增字段 取记录的时候用 select ('JK'+CONVERT(nvarchar(20),id)) as 你的列名 from 你的表
CREATE FUNCTION f_NextBH() RETURNS char(8) AS BEGIN RETURN(SELECT 'JK'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK)) END
alter table tb add constraint bh_df default dbo.f_nextbh() for col with values
if exists(select 1 from sys.objects where object_id=object_id('tb_user') and type='u') drop table tb_user create table tb_user(userno nvarchar(12),username nvarchar(20)) create view view_GetNowDate as select getdate() as now alter function GenerateUserNo() returns nvarchar(12) begin declare @UserNo nvarchar(12) declare @Head nvarchar(8) declare @NowDate datetime select @NowDate=now from view_GetNowDate select @Head=convert(varchar(8),@NowDate,112) if not exists(select 1 from tb_user) begin select @UserNo=@Head+'0001' end else begin declare @MaxNo int select @MaxNo=Max(cast (right(UserNo,4) as int)) from tb_user where left(UserNo,8)=@Head select @MaxNo=@MaxNo+1 select @UserNo=@Head+right(('000'+cast(@MaxNo as nvarchar)),4) end return @UserNo end alter table tb_user add constraint constraint_default_value default dbo.GenerateUserNo() for UserNodelete from tb_user insert into tb_user (username)values('test1') insert into tb_user (username)values('test1') insert into tb_user (username)values('test1') insert into tb_user (username)values('test1') insert into tb_user (username)values('test1') insert into tb_user (username)values('test1')select * from tb_user userno username 201005170001 test1 201005170002 test1 201005170003 test1 201005170004 test1 201005170005 test1 201005170006 test1
2、编写一个触发器 for insert
update 表 set 自编号列 = 'JK' + cast([ID] as varchar(20)) where [ID] in (select [ID] from inserted)
计算当前最后一条记录的id,然后+1,但效率有点问题.如果JK字母前缀是固定的,建议生成int型identity自增字段
取记录的时候用 select ('JK'+CONVERT(nvarchar(20),id)) as 你的列名 from 你的表
RETURNS char(8)
AS
BEGIN
RETURN(SELECT 'JK'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
if exists(select 1 from sys.objects where object_id=object_id('tb_user') and type='u')
drop table tb_user
create table tb_user(userno nvarchar(12),username nvarchar(20))
create view view_GetNowDate as
select getdate() as now
alter function GenerateUserNo()
returns nvarchar(12)
begin
declare @UserNo nvarchar(12)
declare @Head nvarchar(8)
declare @NowDate datetime
select @NowDate=now from view_GetNowDate
select @Head=convert(varchar(8),@NowDate,112)
if not exists(select 1 from tb_user)
begin
select @UserNo=@Head+'0001'
end
else
begin
declare @MaxNo int
select @MaxNo=Max(cast (right(UserNo,4) as int)) from tb_user where left(UserNo,8)=@Head
select @MaxNo=@MaxNo+1
select @UserNo=@Head+right(('000'+cast(@MaxNo as nvarchar)),4)
end
return @UserNo
end
alter table tb_user add constraint constraint_default_value default dbo.GenerateUserNo() for UserNodelete from tb_user
insert into tb_user (username)values('test1')
insert into tb_user (username)values('test1')
insert into tb_user (username)values('test1')
insert into tb_user (username)values('test1')
insert into tb_user (username)values('test1')
insert into tb_user (username)values('test1')select * from tb_user userno username
201005170001 test1
201005170002 test1
201005170003 test1
201005170004 test1
201005170005 test1
201005170006 test1