表A id , value
2 900
3 1000
5 860
4 1780
8 808
表B id , value
2 900
3 1000
7 860
1 1780
8 808
10 1000
我要让表B减表A 得到对应的id和value这两列
当B.id=A.id 时 id=B.id , value=B.value-A.value
当B.id在A里面没有时 id=B.id value=B.value
当A.id在B里面没有时 id=A.id value=-A.value
sql语句怎么写，高手指教
------解决方案--------------------
- SQL code
with a as( select 2 id, 900 value from dual union all select 3 id, 1000 value from dual union all select 5 id, 860 value from dual union all select 4 id, 1780 value from dual union all select 8 id, 808 value from dual),b as( select 2 id, 900 value from dual union all select 3 id, 1000 value from dual union all select 7 id, 860 value from dual union all select 1 id, 1780 value from dual union all select 8 id, 808 value from dual union all select 10 id, 1000 value from dual) SELECT nvl(b.id, a.id) id, CASE WHEN a.id IS NOT NULL AND b.id IS NOT NULL THEN b.value - a.value WHEN a.id IS NOT NULL THEN -a.value ELSE b.value END VALUE FROM a FULL OUTER JOIN b ON a.id = b.id;