oracle全连接处理连接字段显示不全问题 |
您所在的位置:网站首页 › oracle连接字段 › oracle全连接处理连接字段显示不全问题 |
现有oracle表 A 表B ID COLA ID COLB 1 1 2 4 2 2 3 5 3 3 4 6 对表A和表B全连接FULL JOIN 操作 SQL1:SELECT T.ID,COLA,COLB FROM (SELECT * FROM A) T FULL JOIN (SELECT * FROM B) R ON T.ID=R.ID 结果是 ID COLA COLB 1 1 2 2 4 3 3 5 6 由于SQL1里选择的是T.ID(就是A的ID),因此全连接后连接字段只会显示A的ID,而B的ID是null
同理如果选择R.ID(就是B的ID)结果如下 A的ID就是null SQL2:SELECT R.ID,COLA,COLB FROM (SELECT * FROM A) T FULL JOIN (SELECT * FROM B) R ON T.ID=R.ID ID COLA COLB 1 2 2 4 3 3 5 4 6
对结果表处理下即可 SQL3 : SELECT A||B AS ID,COLA,COLB FROM (SELECT T.ID AS A,R.ID AS B,COLA,COLB FROM (SELECT * FROM A) T FULL JOIN (SELECT * FROM B) R ON T.ID=R.ID) WHERE A IS NULL OR B IS NULL UNION ALL SELECT A AS ID,COLA,COLB FROM (SELECT T.ID AS A,R.ID AS B,COLA,COLB FROM (SELECT * FROM A) T FULL JOIN (SELECT * FROM B) R ON T.ID=R.ID) WHERE A IS NOT NULL AND B IS NOT NULL 得到将表A 表B 里的ID取别名 A B ,然后把AB列融合成1列ID,即AB同时不为空的那么取其中一个即可,union 上AB有一个为空的,取A||B即可 A B COLA COLB 1 1 2 2 2 4 3 3 3 5 4 6 最后得到
ID COLA COLB 1 1 2 2 4 3 3 5 4 6
|
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |