SQL SERVER 如何处理带字母的自增列--【叶子】

--需求说明: /* id col ---------- ---------- AB00001 a AB00002 b --当再插入数据的时候让id自动变成AB00003 */ --1.求最大值法（高并发时不适用，只是介绍个思路） --测试数据 if object_id('[macotb]') is not null drop table [macotb] create table [macotb] (id varchar(7),col varchar(1)) insert into [macotb] select 'AB00001','a' union all select 'AB00002','b' declare @max varchar(7) select @max='AB'+right('00000'+ltrim(max(replace(id,'AB','')+1)),5) from [macotb] insert into [macotb] select @max,'c' select * from [macotb] /* id col ------- ---- AB00001 a AB00002 b AB00003 c */ --2.利用@@identity，分步处理 if object_id('[macotb]') is not null drop table [macotb] create table [macotb] ([no] int identity,id varchar(7),col varchar(1)) insert into [macotb] select 'AB00001','a' union all select 'AB00002','b' insert into [macotb](col) select 'c' update [macotb] set id='AB'+right('00000'+ltrim([no]),5) where [no]=@@identity select id,col from [macotb] /* id col ------- ---- AB00001 a AB00002 b AB00003 c */ --3.直接添加运算列 if object_id('[macotb]') is not null drop table [macotb] create table [macotb] ( [no] int identity, id as ('AB'+right('00000'+ltrim([no]),5)), col varchar(1) ) insert into [macotb](col) select 'a' union all select 'b' select id,col from [macotb] /* id col ------------ ---- AB00001 a AB00002 b */ insert into [macotb](col) select 'c' union all select 'd' select id,col from [macotb] /* id col ------------ ---- AB00001 a AB00002 b AB00003 c AB00004 d */ --叶子建议使用第三种方式！