--需求说明:
/*
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
*/
--叶子建议使用第三种方式！
摘自 沧海一叶点滴心得
|