求两个表相差

 

例表名：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) z3from(  select x1 ,sum(y1) y1, sum(z1) z1 from a group by x1) mfull join(  select x2 ,sum(y2) y2, sum(z2) z2 from b group by x2) non 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)goselect isnull(m.x1 , n.x2) x3 ,       isnull(m.y1 , 0) - isnull(n.y2,0) y3,       isnull(m.z1 , 0) - isnull(n.z2,0) z3from(  select x1 ,sum(y1) y1, sum(z1) z1 from a group by x1) mfull join(  select x2 ,sum(y2) y2, sum(z2) z2 from b group by x2) non m.x1 = n.x2drop table a , b/*x3         y3          z3          ---------- ----------- ----------- hh         2           2jj         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)goselect x1 x3, sum(y1) y3 , sum(z1) z3 from(select x1 , y1 , z1 from aunion allselect x2 x1 , -y2 y1 , -z2 z1 from b) tgroup by x1drop table a , b/*x3         y3          z3          ---------- ----------- ----------- hh         2           2jj         1           1（所影响的行数为 2 行）*/