这个sql咋写？

这个sql咋写？

两个表的结构一模一样，数据大部分是重复，小部分不同；

要求：

1.如果a列数据相同，计算b列相减的结果

test1:

a    b 

xxx  1

yyy  2

zzz  3

 

test2：

a    b

xxx  3

zzz  2

qqq  1

查询结果结果为：

a    b

xxx  2

zzz  -1

yyy  -2

qqq  1 

 

Create Table:

CREATE TABLE test1(a VARCHAR2(20),b NUMBER);CREATE TABLE test2(a VARCHAR2(20),b NUMBER);INSERT INTO test1 VALUES('xxx',1);INSERT INTO test1 VALUES('yyy',2);INSERT INTO test1 VALUES('zzz',3);INSERT INTO test2 VALUES('xxx',3);INSERT INTO test2 VALUES('zzz',2);INSERT INTO test2 VALUES('qqq',1);SELECT * FROM test1;SELECT * FROM test2;

 

Solution 1:

SELECT a ,SUM(c) AS bFROM (SELECT a ,-1 * b AS c FROM test1 UNION ALL SELECT a ,b AS c FROM test2)GROUP BY a;A B-------------------- ----------yyy -2zzz -1xxx 2qqq 1

 

Solution 2:

SELECT nvl(t2.a, t1.a) a ,(nvl(t2.b, 0) - nvl(t1.b, 0)) bFROM test2 t2FULL OUTER JOIN test1 t1 ON (t2.a = t1.a);A B-------------------- ----------xxx 2yyy -2zzz -1qqq 1

 

 

来自：CU-ORALCE群

JOIN复习:

 

SQL> SELECT nvl(t2.a, t1.a) a 2 ,(nvl(t2.b, 0) - nvl(t1.b, 0)) b 3 FROM test2 t2 4 RIGHT JOIN test1 t1 ON (t2.a = t1.a); A B-------------------- ----------xxx 2zzz -1yyy -2SQL> SQL> SELECT nvl(t2.a, t1.a) a 2 ,(nvl(t2.b, 0) - nvl(t1.b, 0)) b 3 FROM test2 t2,test1 t1 4 WHERE t2.a(+) = t1.a; A B-------------------- ----------xxx 2zzz -1yyy -2

 

 

 

 

 

 SQL> SELECT nvl(t2.a, t1.a) a 2 ,(nvl(t2.b, 0) - nvl(t1.b, 0)) b 3 FROM test2 t2 4 LEFT JOIN test1 t1 ON (t2.a = t1.a); A B-------------------- ----------xxx 2zzz -1qqq 1 SQL> SQL> SELECT nvl(t2.a, t1.a) a 2 ,(nvl(t2.b, 0) - nvl(t1.b, 0)) b 3 FROM test2 t2,test1 t1 4 WHERE t2.a = t1.a(+); A B-------------------- ----------xxx 2zzz -1qqq 1