create table #table1 (id int,name char(1),number1 int)
insert #table1 values(0,'a',9)
insert #table1 values(1,'b',8)create table #table2 (id int,name char(1),number2 int)
insert #table2 values(0,'a',7)
insert #table2 values(2,'c',8)
select id,name,sum(number1) number1,sum(number2) number2,sum(number1+number2) total from ( select id,name,number1,0 number2 from #table1
union all
select id,name,0 number1,number2 from #table2) tem group by id,name
drop table #table1
drop table #table2
insert #table1 values(0,'a',9)
insert #table1 values(1,'b',8)create table #table2 (id int,name char(1),number2 int)
insert #table2 values(0,'a',7)
insert #table2 values(2,'c',8)
select id,name,sum(number1) number1,sum(number2) number2,sum(number1+number2) total from ( select id,name,number1,0 number2 from #table1
union all
select id,name,0 number1,number2 from #table2) tem group by id,name
drop table #table1
drop table #table2
isnull(t1.number1,0) as number1, isnull(t1.number2,0) as number2 ,
isnullisnull(t1.number1,0)+isnull(t1.number2,0) as total
from t1 full join t2 on t1.id=t2.id
使用全连接就可以了 full join onselect isnull(t1.id,t2.id) as id,isnull(t1.name,t2.name) as name,
isnull(t1.number1,0) as number1, isnull(t2.number2,0) as number2 ,
isnullisnull(t1.number1,0)+isnull(t2.number2,0) as total
from t1 full join t2 on t1.id=t2.id
isnull(t1.number1,0) as number1, isnull(t2.number2,0) as number2 ,
isnullisnull(t1.number1,0)+isnull(t2.number2,0) as total
from t1 full join t2 就可以了