create table A(a1 varchar(20),a2 varchar(10),a3 varchar(10),a4 varchar(10),a5 varchar(10))
insert A select 'z-12-01','c','m','d','200501'
insert B(b1,b2,b3)
select b1=case when left(a1,1)='z' then '1'
when left(a1,1)='g' then '2'
when left(a1,1)='c' then '3'
end+'-'+case when a5='200501' then '040901'
when a5='200603' then '200501'
end+'-'+right('000'+(select aa=isnull(count(case when left(a1,1)='z' then '1'
when left(a1,1)='g' then '2'
when left(a1,1)='c' then '3'
end+'-'+case when a5='200501' then '040901'
when a5='200603' then '200501'
end),0) from a),3),
b2='9'+a3,
a5
from a
200501>>040901
200601>>041001
200609>>050101
200511>>050201
……
insert @a select 'z-12-01', 'c', 'm', 'd', '200501'
insert @a select 'z-12-02','c','d','m','200501'declare @b table(b1 varchar(199), b2 varchar(10), b3 varchar(19))
insert @b select '1-040901-001', '9m', '200501'select a1,a2,a3,a4,a5,
a6=(select count(1) from @a where left(a1,1)=left(a.a1,1) and a5=a.a5 and a1>=a.a1),
a7=left(a1,1),
a8=case when a5='200501' then '040901'
else '200501' end
from @a aselect b1=case a7 when 'z' then '1'
when 'g' then '2'
when 'c' then '3'
end
+'-'+a8+'-'+right('000'+ltrim(a6),3),
b2='数字'+a3,
b3=a5
from(
select a1,a2,a3,a4,a5,
a6=(select count(1) from @a where left(a1,1)=left(a.a1,1) and a5=a.a5 and a1>=a.a1),
a7=left(a1,1),
a8=case when a5='200501' then '040901'
else '200501'
end
from @a a
)b
insert code
select '200501','040901' union all
select '200601','041001' union all
select '200609','050101' union all
select '200511','050201'create table A(a1 varchar(20),a2 varchar(10),a3 varchar(10),a4 varchar(10),a5 varchar(10))
insert A select 'z-12-01','c','m','d','200501'
insert B(b1,b2,b3)
select b1=case when left(a1,1)='z' then '1'
when left(a1,1)='g' then '2'
when left(a1,1)='c' then '3'
end+'-'+ b.toid+'-'+
right('000'+(select aa=isnull(count(case when left(a1,1)='z' then '1'
when left(a1,1)='g' then '2'
when left(a1,1)='c' then '3'
end+'-'+b.toid),0)
from a,code b
where a.a5=b.id ),3),
b2='9'+a3,
a5
from a ,code b
where a.a5=b.id
insert code
select '200501','040901' union all
select '200601','041001' union all
select '200609','050101' union all
select '200511','050201'create table A(a1 varchar(20),a2 varchar(10),a3 varchar(10),a4 varchar(10),a5 varchar(10))
insert A select 'z-12-01','c','m','d','200501'create table B(b1 varchar(20),b2 varchar(20),b3 varchar(20))
insert B(b1,b2,b3)select b1=case when left(a1,1)='z' then '1'
when left(a1,1)='g' then '2'
when left(a1,1)='c' then '3'
end+'-'+ b.toid+'-'+
right(1000+(select isnull(count(1),0)+1
from b
where left(b.b1,8)= case when left(a1,1)='z' then '1'
when left(a1,1)='g' then '2'
when left(a1,1)='c' then '3'
end+ substring(b.b1,2,7)),3),
b2='9'+a3,
a5
from a ,code b
where a.a5=b.id
insert code
select '200501','040901' union all
select '200601','041001' union all
select '200609','050101' union all
select '200511','050201'create table A(a1 varchar(20),a2 varchar(10),a3 varchar(10),a4 varchar(10),a5 varchar(10))
insert A select 'z-12-01','c','m','d','200501'create table B(b1 varchar(20),b2 varchar(20),b3 varchar(20))insert B(b1,b2,b3)
select b1=case when left(a1,1)='z' then '1'
when left(a1,1)='g' then '2'
when left(a1,1)='c' then '3'
end+'-'+ c.toid+'-'+
right(1000+(select isnull(count(1),0)+1
from b
where left(b.b1,8)= case when left(a1,1)='z' then '1'
when left(a1,1)='g' then '2'
when left(a1,1)='c' then '3'
end+'-'+ c.toid),3),
b2='9'+a3,
a5
from a ,code c
where a.a5=c.id
a1 a2 a3 a4 a5
z-12-01 c m d 200501B
b1 b2 b3
1-040901-001 9m 200501B表中的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
有对应表
200501>>040901
200601>>041001
200609>>050101
200511>>050201建议用程序做吧.定义三个字符串,s1 , s2 , s3
分别取值,然后insert
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')
goif object_id('pubs..D') is not null
drop table D
go
select id = identity(int,1,1) , * into D from Adeclare @count as int
select @count = count(*) from ddeclare @i as int
set @i = 1declare @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
endselect * from Bdrop 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 行）
*/
如果b表的列类型为char，而a表为varchar和numeric,那insert语句该如何写呢？