sql server处理带字母的自增列的三种方式

--需求说明:
/*
id col
---------- ----------
AB00001a
AB00002b
--当再插入数据的时候让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
*/
 
--建议使用第三种方式！