使用FULL OUTER JOIN。
select isnull(a.f1,b.f1) as f1,isnull(a.f2,b.f2) as f2,a.f3,b.f4
from table1 a full outer join table2 b on a.f1 = b.f1 and a.f2 = b.f2以上是表1和表2的连接,把得到的结果集作为一个新的表和表3再进行外连接,最后就可以得到你要的结果,完整sql语句就不写了,思路就是这样。
select isnull(a.f1,b.f1) as f1,isnull(a.f2,b.f2) as f2,a.f3,b.f4
from table1 a full outer join table2 b on a.f1 = b.f1 and a.f2 = b.f2以上是表1和表2的连接,把得到的结果集作为一个新的表和表3再进行外连接,最后就可以得到你要的结果,完整sql语句就不写了,思路就是这样。
select isnull(TABLE1.字段1,ISNULL(TABLE2.字段1,ISNULL(TABLE3.字段1,TABLE4.字段1))) as 字段1,
isnull(TABLE1.字段2,ISNULL(TABLE2.字段2,ISNULL(TABLE3.字段2,TABLE4.字段2))) as 字段2,
字段3,字段4,字段5
FROM TABLE1 FULL OUTER JOIN
TABLE3 ON
TABLE3.字段1 = TABLE3.字段1 AND
TABLE1.字段2 = TABLE3.字段2 FULL OUTER JOIN
TABLE4 ON
TABLE3.字段1 = TABLE4.字段1 AND
TABLE3.字段2 = TABLE4.字段2 FULL OUTER JOIN
TABLE2 ON
TABLE4.字段1 = TABLE2.字段1 AND
TABLE4.字段2 = TABLE2.字段2 字段1 字段2 字段3 字段4 字段5
XX 101 10 NULL 15
XX 101 10 NULL 8
XX 102 20 NULL NULL
ZZ 103 NULL NULL NULL
YY 101 NULL 5 NULL
XX 101 NULL 20 NULL 为什么有些数据没有合并?
应该是:
select isnull(x.f1,c.fl) as f1,isnull(x.f2,c.f2) as f2,x.f3,x.f4,c.f5 from
(select isnull(a.f1,b.f1) as f1,isnull(a.f2,b.f2) as f2,a.f3,b.f4
from table1 a full outer join table2 b on a.f1 = b.f1 and a.f2 = b.f2) as x full outer join table3 c on x.f1 = c.f1 and x.f2 = c.f2把table4也按这种方式加上就行了,本来想少写点,结果写得更多~~