求一个SQL...求指导
本帖最后由 kenticny0270 于 2012-12-05 21:37:54 编辑 需求：有两张表 tableA 和 tableB
tableA 的字段有 customid managerid deposit
tableB 的字段有 customid managerid loan
两张表都是以（customid，managerid）作为唯一标识
现在要有个查询，查询到的字段为customid managerid deposit loan
tableA和tableB中的customid和managerid 有部分重复，还有一部分是不同的。
如果customid和managerid同时存在在两张表中，就合并
例如：
tableA customid managerid deposit
1001 3000 4000
tableB customid managerid loan
1001 3000 3000
查询结果 customid managerid deposit loan
1001 3000 4000 3000
如果customid和managerid单独存在的话：
例如：
tableA customid managerid deposit
1001 2000 3000
tableB customid managerid loan
2001 3000 4000
查询结果 customid managerid deposit loan
1001 2000 3000 NULL
2001 3000 NULL 4000
需求说完了...不知道说清楚了么....求各位大神指教....
[解决办法]
用full join就可以了。
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;
--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
--------------------------------
1001300040005000
100120003000
1002800047000
