数据库三表合一

t1

t2

t3

t4

目标：将t1,t3,t4的表内容合并到t2中

/*插入只有t1有的内容*/
insert into t2	(a,b)
SELECT * 
from t1 where not EXISTS(
	SELECT * from t3 where t1.a=t3.a
)AND not EXISTS(
	SELECT * from t4 where t1.a=t4.a
);
/*插入只有t1和t4有的内容*/
insert into t2	(a,b,c,f)
SELECT t1.a,t1.b,t4.c,t4.f
from t1,t4 where not EXISTS(
	SELECT * from t3 where t1.a=t3.a
)and t1.a=t4.a;
/*插入只有t2有的内容*/
insert into t2	(a,c,d,e)
SELECT * 
from t3 where not EXISTS(
	SELECT * from t1 where t3.a=t1.a
)AND not EXISTS(
	SELECT * from t4 where t3.a=t4.a
);
/*插入只有t1,t3有的内容*/
insert into t2	(a,b,c,d,e)
SELECT t1.a,t1.b,t3.c,t3.d,t3.e
from t1,t3 where not EXISTS(
	SELECT * from t4 where t1.a=t4.a
)and t1.a=t3.a;
/*插入只有t4有的内容*/
insert into t2	(a,c,f)
SELECT * 
from t4 where not EXISTS(
	SELECT * from t1 where t4.a=t1.a
)AND not EXISTS(
	SELECT * from t3 where t4.a=t3.a
);
/*插入只有t3,t4有的内容*/
insert into t2	(a,c,d,e,f)
SELECT t3.a,t3.c,t3.d,t3.e,t4.f
from t4,t3 where not EXISTS(
	SELECT * from t1 where t1.a=t4.a
)and t4.a=t3.a;
/*插入都有的内容*/
insert into t2	(a,b,c,d,e,f)
SELECT t1.a,t1.b,t3.c,t3.d,t3.e,t4.f 
FROM t1,t3,t4 where t1.a=t3.a and t1.a=t4.a;

最后

t2