【SQL server】几个字段的拼接，实现字段的自增

目标：将几段数字拼接为图号

条件：现有四个字段，前三个可通过表A获取，第四个字段为自增字段

思路：1.通过select 表A 获取前三个字段

          2.通过declare @变量 先赋初始值给该字段，在通过update @变量+1 来实现该字段的自增

          3.使用cast(字段 as  数据类型)将四个字段类型设置为同一个

表1

ID	名称	数值
1	字段1	AA
2	字段1	AA
3	字段1	BB
4	字段1	BB
5	字段2	001
6	字段2	002
7	字段2	003
8	字段2	004
9	字段3	1
10	字段3	2
11	字段3	1
12	字段3	2

表2

ID	name	字段1	字段2	字段3	字段4	图号
1	A1	1	5	9	1000	1AA.001.1000
2	A2	2	6	10	1001	2AA.002.1001
3	B1	3	7	11	3000	BB1.001.3000
4	B2	4	8	12	3001	BB2.002.3001

当字段1=BB时，要得到的图号为：字段1字段3.字段2.字段4
当字段1=AA时，要得到的图号为：字段3字段1.字段2.字段4

--实现字段4赋初始值以及自增
declare @num1 varchar(4),@num2 varchar(4)
set @num1=(select 字段4 from 表2 where ID in (select max(ID) from 表2 where 字段1=AA and 字段4!=' ' )) 
set @num2=(select '0'+RTRIM(LTRIM(str(字段4))) from 表2 where ID in (select max(ID) from 表2 where 字段1=BB and 字段4!=' ' )) 
update 表2 set 字段4=(case when (select count(1) from 表2 where 字段1=AA)!=1 then  @num1+1
                                 else  '1000' end) where 字段1=AA  and 字段4=' ' or 字段4 is null 
update 表2 set 字段4=(case when (select count(1) from 表2 where 字段1=BB)!=1 then  '0'+RTRIM(LTRIM(str(@num2+1)))
                                 else  '0350' end) where 字段1=BB and 字段4=' ' or 字段4 is null 

--图号拼接
drop table th
select a.字段1 as num1,b.字段2 as num2,c.字段3 as num3 ,d.字段4 as order_num,d.id into th
from 表1 a,表1 b,表1 c,表2 d 
where a.id=d.字段1 and b.id=d.字段2 and c.id=d.字段3 
drop table th1
SELECT CAST(num1 AS VARCHAR(10)) + CAST(num2 AS VARCHAR(10))+'.'+ CAST(num3 AS VARCHAR(10))+'.' + CAST(order_num AS VARCHAR(10) ) as num,num1 as name ,id into th1 FROM th where num1!='AA'
update a set a.图号=b.num from 表2 a,th1 b where a.id=b.id
drop table th1
SELECT CAST(num2 AS VARCHAR(10)) + CAST(num1 AS VARCHAR(10))+CAST(num3 AS VARCHAR(10))+ CAST(order_num AS VARCHAR(10) ) as num,num1 as name ,id into th1 FROM th where num1='AA'
update a set a.图号=b.num from 表2 a,th1 b where a.id=b.id