eg: 三个表select IsNull(IsNull(a.itemnum,b.itemnul),c.itemnum),
a.quantity1,b.quantity2,c.quntity3
from table1 a
full join table2 b on a.itemnum = b.itemnum
full join table3 c on a.itemnum = c.itemnum
a.quantity1,b.quantity2,c.quntity3
from table1 a
full join table2 b on a.itemnum = b.itemnum
full join table3 c on a.itemnum = c.itemnum
into newtablename
from a full join b on a.itemnum=b.itemnum
full join c on a.itemnum=c.itemnum
full join ...
where a.itemnum=b.itemnum and a.itemnum=c.itemnum
a.quantity1,b.quantity2,c.quntity3
from table1 a
full join table2 b on a.itemnum = b.itemnum
full join table3 c on isnull(a.itemnum,b.itemnum) = c.itemnum
a.quantity1,b.quantity2,c.quntity3
from table1 a
full join table2 b on a.itemnum = b.itemnum
full join table3 c on a.itemnum = c.itemnum
--处理
declare @i int,@ch varchar(1)
,@fd1 varchar(8000),@fd2 varchar(8000),@tb varchar(8000)select @i=1,@ch='a'
,@fd1='a.itemnum'
,@fd2=',quantity1=sum(a.quantity1)'
,@tb=''
select @i=@i+1,@ch=char(ascii(@ch)+1)
,@fd1='isnull('+@fd1+','+@ch+'.itemnum)'
,@fd2=@fd2+',quantity'+cast(@i as varchar)
+'=sum('+@ch+'.quantity'+cast(@i as varchar)+')'
,@tb=@tb+' full join ['+name+'] '+@ch+' on a.itemnum='
+@ch+'.itemnum'
from sysobjects where name like 'table%' and name<>'table1'
exec('select itemnum='+@fd1+@fd2+'
from table1 a '+@tb+' group by '+@fd1)
create table table1(itemnum varchar(4),quantity1 int)
insert into table1
select 'a001',10
union all select 'a002',105
union all select 'a005',5create table table2(itemnum varchar(4),quantity2 int)
insert into table2
select 'a001',25
union all select 'a002',5
union all select 'a003',16create table table3(itemnum varchar(4),quantity3 int)
insert into table3
select 'a001',25
union all select 'a003',16create table table4(itemnum varchar(4),quantity4 int)
insert into table4
select 'a011',25
union all select 'a013',16go--N个表的,用动态语句:
--处理
declare @i int,@ch varchar(1)
,@fd1 varchar(8000),@fd2 varchar(8000),@tb varchar(8000)select @i=1,@ch='a'
,@fd1='a.itemnum'
,@fd2=',quantity1=sum(a.quantity1)'
,@tb=''
select @i=@i+1,@ch=char(ascii(@ch)+1)
,@fd1='isnull('+@fd1+','+@ch+'.itemnum)'
,@fd2=@fd2+',quantity'+cast(@i as varchar)
+'=sum('+@ch+'.quantity'+cast(@i as varchar)+')'
,@tb=@tb+' full join ['+name+'] '+@ch+' on a.itemnum='
+@ch+'.itemnum'
from sysobjects where name like 'table%' and name<>'table1'
exec('select itemnum='+@fd1+@fd2+'
into tablenew from table1 a '+@tb+' group by '+@fd1)--显示生成的表结果
select * from tablenew
go--删除测试环境
drop table table1,table2,table3,table4,tablenew/*--测试结果
itemnum quantity1 quantity2 quantity3 quantity4
------- ----------- ----------- ----------- -----------
a001 10 25 25 NULL
a002 105 5 NULL NULL
a003 NULL 16 16 NULL
a005 5 NULL NULL NULL
a011 NULL NULL NULL 25
a013 NULL NULL NULL 16(所影响的行数为 6 行)
--*/