这个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 b FROM (SELECT a ,-1 * b AS c FROM test1 UNION ALL SELECT a ,b AS c FROM test2) GROUP BY a; A B -------------------- ---------- yyy -2 zzz -1 xxx 2 qqq 1

 

Solution 2:

SELECT nvl(t2.a, t1.a) a ,(nvl(t2.b, 0) - nvl(t1.b, 0)) b FROM test2 t2 FULL OUTER JOIN test1 t1 ON (t2.a = t1.a); A B -------------------- ---------- xxx 2 yyy -2 zzz -1 qqq 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 2 zzz -1 yyy -2 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 2 zzz -1 yyy -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 2 zzz -1 qqq 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 2 zzz -1 qqq 1