这个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原文链接：