将A表部分数据修改后倒入B表，解决的另开贴送200分
A
a1 a2 a3 a4 a5
z-12-01 c m d 200501
B
b1 b2 b3
1-040901-001 9m 200501
B表中的b1字段由三部分组成，第一部分对应A.a1中的第一部分，如果A.a1是z的话就为1，g为2，c为3；第二部分对应A.a5，如果是200501就为040901,200603为200501等等有个对应关系；第三部分是在前两部分相同的情况下自动加一。
B表中的b2字段则对应A.a3，只不过在其前面加个数字。
B.b3=A.a5
[解决办法]
if object_id( 'pubs..A ') is not null
drop table A
go
create table A(a1 varchar(20),a2 varchar(20),a3 varchar(20),a4 varchar(20),a5 varchar(20))
insert into A(a1,a2,a3,a4,a5) values( 'z-12-01 ', 'c ', 'm ', 'd ', '200501 ')
insert into A(a1,a2,a3,a4,a5) values( 'g-12-01 ', 'c ', 'm ', 'd ', '200601 ')
insert into A(a1,a2,a3,a4,a5) values( 'c-12-01 ', 'c ', 'm ', 'd ', '200501 ')
insert into A(a1,a2,a3,a4,a5) values( 'z-12-01 ', 'c ', 'm ', 'd ', '200501 ')
insert into A(a1,a2,a3,a4,a5) values( 'g-12-01 ', 'c ' , 'm ', 'd ', '200501 ')
insert into A(a1,a2,a3,a4,a5) values( 'z-12-01 ', 'c ', 'm ', 'd ', '200601 ')
go
if object_id( 'pubs..B ') is not null
drop table B
go
create table B(b1 varchar(20),b2 varchar(20),b3 varchar(20))
if object_id( 'pubs..C ') is not null
drop table C
go
create table C(a5 varchar(20),b5 varchar(20))
insert into C(a5,b5) values( '200501 ', '040901 ')
insert into C(a5,b5) values( '200601 ', '041001 ')
go
if object_id( 'pubs..D ') is not null
drop table D
go
select id = identity(int,1,1) , * into D from A
declare @count as int
select @count = count(*) from d
declare @i as int
set @i = 1
declare @s1 as varchar(20)
declare @s2 as varchar(20)
declare @s3 as varchar(20)
declare @s4 as varchar(20)
declare @s5 as varchar(20)
while @i <= @count
begin
set @s1 = null
set @s2 = null
set @s3 = null
set @s4 = null
set @s5 = null
select @s1 = left(a1,1) from D where id = @i
if @s1 = 'z ' set @s1 = '1 '
if @s1 = 'g ' set @s1 = '2 '
if @s1 = 'c ' set @s1 = '3 '
select @s2 = C.b5 from D,C where D.id = @i and D.a5 = C.a5
select @s3 = max(b1) from B where left(b1,8) = @s1 + '- ' + @s2
if @s3 is null or len(rtrim(@s3)) = 0 set @s3 = '001 '
else
begin
set @s3 = right( '000 ' + cast(cast(right(@s3,3) as int) + 1 as varchar),3)
end
select @s4 = '9 ' + a3 from D where id = @i
select @s5 = a5 from D where id = @i
insert into b(b1,b2,b3) values(@s1 + '- ' + @s2 + '- ' + @s3,@s4,@s5)
set @i = @i + 1
end
select * from B
drop table A,B,C,D
/*
b1 b2 b3
-------------------- -------------------- --------------------
1-040901-001 9m 200501
2-041001-001 9m 200601
3-040901-001 9m 200501
1-040901-002 9m 200501
2-040901-001 9m 200501
1-041001-001 9m 200601
（所影响的行数为 6 行）
*/
