17,090
社区成员
发帖
与我相关
我的任务
分享
select decode(a.id,null,b.id,a.id) id ,decode(a.mid,null,b.mid,a.mid) mid , a.did,b.pid from a full join b on a.id = b.id and a.mid = b.mid
--db2上测试,
with taba as(
select '1001' cid,'3000' mid,4000 dps from sysibm.sysdummy1
union all
select '1002' cid,'8000' mid,47000 dps from sysibm.sysdummy1),
tabb as(
select '1001' cid,'2000' mid,3000 ln from sysibm.sysdummy1
union all
select '1001' cid,'3000' mid,5000 ln from sysibm.sysdummy1)
select case when a.cid is null then b.cid else a.cid end,
case when a.mid is null then b.mid else a.mid end,
dps,ln
from taba a full join tabb b
on a.cid=b.cid
and a.mid=b.mid
--------------------------------
1001 3000 4000 5000
1001 2000 3000
1002 8000 47000
select greatest(nvl(tableA.customid,0),nvl(tableB.customid,0)) customid,
greatest(nvl(tableA.managerid,0),nvl(tableB.managerid,0)) managerid,
deposit,loan from
tableA full join tableB on
tableA.customid=tableB.customid and tableA.managerid=tableB.managerid;