select identity(int,1,1)as a, name, price1, price2 into #tmp1 from test1 where class ='y'; select identity(int,1,1)as a, name, price1, price2 into #tmp2 from test1 where class ='x'; insert into test2 select #tmp1.name,#tmp1.price1,#tmp1.price2,#tmp2.price1,#tmp2.price2 from #tmp1 full join #tmp2 on #tmp2.a = #tmp1.a
我这里上CSDN不太稳定,如果您方便的话能否在QQ上请教,我的QQ是4714407
select identity(int,1,1)as a, name, price1, price2 into #tmp1 from test1 where class ='Y'; select identity(int,1,1)as a, name, price1, price2 into #tmp2 from test1 where class ='X'; insert into test2 select isnull(#tmp1.name,#tmp2.name),#tmp1.price1,#tmp1.price2,#tmp2.price1,#tmp2.price2 from #tmp1 full join #tmp2 on #tmp2.a = #tmp1.a and #tmp1.name=#tmp2.name
全部测试代码: 另外, 我现在不能上QQ,SORRY drop table test1 go create table test1 ( name varchar(4),price1 int , price2 int, class varchar(1)) go drop table test2 go create table test2 ( name varchar(4),price1 int , price2 int, price3 int , price4 int) go insert into test1 values ('A', 1 , 2 , 'X') insert into test1 values ('A', 2 , 2 , 'X') insert into test1 values ('A', 2 , 2 , 'X') insert into test1 values ('A', 3 , 4 , 'Y') insert into test1 values ('A', 5 , 4 , 'Y') insert into test1 values ('B', 4 , 4 , 'X') insert into test1 values ('B', 5 , 4 , 'Y') insert into test1 values ('B', 6 , 6 , 'Y') go drop table #tmp1 go drop table #tmp2 go truncate table test2 go select identity(int,1,1)as a, name, price1, price2 into #tmp1 from test1 where class ='Y'; select identity(int,1,1)as a, name, price1, price2 into #tmp2 from test1 where class ='X'; insert into test2 select isnull(#tmp1.name,#tmp2.name),#tmp1.price1,#tmp1.price2,#tmp2.price1,#tmp2.price2 from #tmp1 full join #tmp2 on #tmp2.a = #tmp1.a and #tmp1.name=#tmp2.name select * from #tmp1 select * from #tmp2 select * from test2
select identity(int,1,1)as a, name, price1, price2 into #tmp2 from test1 where class ='x';
insert into test2 select #tmp1.name,#tmp1.price1,#tmp1.price2,#tmp2.price1,#tmp2.price2 from #tmp1 full join #tmp2
on #tmp2.a = #tmp1.a
select identity(int,1,1)as a, name, price1, price2 into #tmp2 from test1 where class ='X';
insert into test2 select isnull(#tmp1.name,#tmp2.name),#tmp1.price1,#tmp1.price2,#tmp2.price1,#tmp2.price2 from #tmp1 full join #tmp2
on #tmp2.a = #tmp1.a and #tmp1.name=#tmp2.name
另外, 我现在不能上QQ,SORRY
drop table test1
go
create table test1 ( name varchar(4),price1 int , price2 int, class varchar(1))
go
drop table test2
go
create table test2 ( name varchar(4),price1 int , price2 int, price3 int , price4 int)
go
insert into test1 values ('A', 1 , 2 , 'X')
insert into test1 values ('A', 2 , 2 , 'X')
insert into test1 values ('A', 2 , 2 , 'X')
insert into test1 values ('A', 3 , 4 , 'Y')
insert into test1 values ('A', 5 , 4 , 'Y')
insert into test1 values ('B', 4 , 4 , 'X')
insert into test1 values ('B', 5 , 4 , 'Y')
insert into test1 values ('B', 6 , 6 , 'Y')
go
drop table #tmp1
go
drop table #tmp2
go
truncate table test2
go
select identity(int,1,1)as a, name, price1, price2 into #tmp1 from test1 where class ='Y';
select identity(int,1,1)as a, name, price1, price2 into #tmp2 from test1 where class ='X';
insert into test2 select isnull(#tmp1.name,#tmp2.name),#tmp1.price1,#tmp1.price2,#tmp2.price1,#tmp2.price2 from #tmp1 full join #tmp2
on #tmp2.a = #tmp1.a and #tmp1.name=#tmp2.name
select * from #tmp1
select * from #tmp2
select * from test2