select isnull(a.id,b.kid) as sid, isnull(a.salary,0)+isnull(b.ksalary,0) as Ssalary from taba a full outer join tabb b on a.id=b.kid
select A.Sid A.sum(Ssalary) from (select id as Sid, salary as Ssalary union all select Kid as Sid, ksalary as Ssalary tabb) as Agroup by Sid
if object_id('pubs..taba') is not null drop table taba go if object_id('pubs..tabb') is not null drop table tabb gocreate table taba(id int,salary int) insert into taba select 1,43987 insert into taba select 2,112144 insert into taba select 3,1142144 insert into taba select 4,487 insert into taba select 5,43987 go create table tabb(id int,ksalary int) insert into tabb select 1,123 insert into tabb select 2,2123 insert into tabb select 3,3123 insert into tabb select 4,4123 insert into tabb select 5,5123 insert into tabb select 6,6123 insert into tabb select 7,76123 goselect id , sum(cash) as cash from ( select id , salary as cash from taba union all select id , ksalary as cash from tabb ) a group by iddrop table taba drop table tabbid cash ----------- ----------- 1 44110 2 114267 3 1145267 4 4610 5 49110 6 6123 7 76123(所影响的行数为 7 行)
isnull(a.id,b.kid) as sid,
isnull(a.salary,0)+isnull(b.ksalary,0) as Ssalary
from
taba a
full outer join
tabb b
on
a.id=b.kid
select A.Sid A.sum(Ssalary) from (select id as Sid, salary as Ssalary union all select Kid as Sid, ksalary as Ssalary tabb) as Agroup by Sid
drop table taba
go
if object_id('pubs..tabb') is not null
drop table tabb
gocreate table taba(id int,salary int)
insert into taba select 1,43987
insert into taba select 2,112144
insert into taba select 3,1142144
insert into taba select 4,487
insert into taba select 5,43987
go
create table tabb(id int,ksalary int)
insert into tabb select 1,123
insert into tabb select 2,2123
insert into tabb select 3,3123
insert into tabb select 4,4123
insert into tabb select 5,5123
insert into tabb select 6,6123
insert into tabb select 7,76123
goselect id , sum(cash) as cash from
(
select id , salary as cash from taba
union all
select id , ksalary as cash from tabb
) a
group by iddrop table taba
drop table tabbid cash
----------- -----------
1 44110
2 114267
3 1145267
4 4610
5 49110
6 6123
7 76123(所影响的行数为 7 行)