求两个表相差

 

例表名：A，B，A列名x1,y1,z1 B列名x2,y2,z2
  A
x1 y1 z1
hh 1 1
jj 2 2
hh 3 3

  B
x2 y2 z2
hh 1 1
jj 1 1
hh 1 1

求SQL语句 ,使A，B表相同字段先求和，再A，B相同字段，相减，
使得查询结果如下：谢谢。

C
x3 y3 z3
hh 2 2
jj 1 1

 

 

SQL code 1:

select isnull(m.x1 , n.x2) x3 ,
       isnull(m.y1 , 0) - isnull(n.y2,0) y3,
       isnull(m.z1 , 0) - isnull(n.z2,0) z3
from
(
  select x1 ,sum(y1) y1, sum(z1) z1 from a group by x1
) m
full join
(
  select x2 ,sum(y2) y2, sum(z2) z2 from b group by x2
) n
on m.x1 = n.x2

验证如下： 

SQL code 1：  

create table a(x1 varchar(10) , y1 int , z1 int)
insert into a values('hh', 1 ,1)
insert into a values('jj', 2 ,2)
insert into a values('hh', 3 ,3)
create table b(x2 varchar(10) , y2 int , z2 int)
insert into b values('hh', 1 ,1)
insert into b values('jj', 1 ,1)
insert into b values('hh', 1 ,1)
go

select isnull(m.x1 , n.x2) x3 ,
       isnull(m.y1 , 0) - isnull(n.y2,0) y3,
       isnull(m.z1 , 0) - isnull(n.z2,0) z3
from
(
  select x1 ,sum(y1) y1, sum(z1) z1 from a group by x1
) m
full join
(
  select x2 ,sum(y2) y2, sum(z2) z2 from b group by x2
) n
on m.x1 = n.x2

drop table a , b

/*
x3         y3          z3          
---------- ----------- ----------- 
hh         2           2
jj         1           1

（所影响的行数为 2 行）

*/ 

--另外一种方法.

SQL code 2: 

create table a(x1 varchar(10) , y1 int , z1 int)
insert into a values('hh', 1 ,1)
insert into a values('jj', 2 ,2)
insert into a values('hh', 3 ,3)
create table b(x2 varchar(10) , y2 int , z2 int)
insert into b values('hh', 1 ,1)
insert into b values('jj', 1 ,1)
insert into b values('hh', 1 ,1)
go

select x1 x3, sum(y1) y3 , sum(z1) z3 from
(
select x1 , y1 , z1 from a
union all
select x2 x1 , -y2 y1 , -z2 z1 from b
) t
group by x1

drop table a , b

/*
x3         y3          z3          
---------- ----------- ----------- 
hh         2           2
jj         1           1

（所影响的行数为 2 行）

*/