create table t1 (id int, a int) create table t2 (id int, a int) insert t1 select 1,100 union all select 3,10 insert t2 select 2,100 union all select 3,5 select * from (select * from t1 union all select * from t2)x where id in( select id from (select * from t1 union all select * from t2)t group by t.id having count(*)=1) union select t1.id,a=t1.a-t2.a from t1,t2 where t1.id=t2.id
declare @t1 table(id int,A int) declare @t2 table(id int,A int) insert into @t1 select 1,100 insert into @t1 select 3,10 insert into @t2 select 2,100 insert into @t2 select 3,5select id ,sum(a) A from (select id,a from @t1 union all select id , case when (select count(id) from @t1 b where b.id=c.id) >=1 then -1 else 1 end *a from @t2 c) t1 group by id
(id int,
a int)
create table t2
(id int,
a int)
insert t1 select 1,100
union all select 3,10
insert t2 select 2,100
union all select 3,5
select * from (select * from t1
union all
select * from t2)x where id in(
select id from
(select * from t1
union all
select * from t2)t group by t.id having count(*)=1)
union
select t1.id,a=t1.a-t2.a from t1,t2 where t1.id=t2.id
declare @t2 table(id int,A int)
insert into @t1 select 1,100
insert into @t1 select 3,10
insert into @t2 select 2,100
insert into @t2 select 3,5select id ,sum(a) A from (select id,a from @t1
union all select id ,
case when (select count(id) from @t1 b where b.id=c.id) >=1
then -1 else 1 end *a from @t2 c) t1
group by id