首先生成tableN 包括这些字段。
然后用insert into tableN(fielda,fieldb,fieldc,fieldd)
select a.fielda,a,fieldb,b.fieldc,c.fieldd from
table1 a,table2 b ,table3 c
where a.fielda=b.fielda=c.fielda=d.fielda
没做测试
然后用insert into tableN(fielda,fieldb,fieldc,fieldd)
select a.fielda,a,fieldb,b.fieldc,c.fieldd from
table1 a,table2 b ,table3 c
where a.fielda=b.fielda=c.fielda=d.fielda
没做测试
insert TableABC as select a.FieldA,a.FieldB,b.FieldC,c.FieldD from FieldA a,FieldB b, FieldC c
不过你的方法也会点问题
如果TableA或TableB或TableC中的FieldA不匹配,则生成的结果会丢掉记录.既结果为:
TableN
FieldA FieldB FieldC FieldD
1 a b c
2 aa null cc
3 null bb null
记录号为4的数据没有了。而我结果集必须要有所有数据!
select tableA.FieldA,tableA.FieldB,tableB.fieldC,tableC.fieldD
from tabelA join tableB on TableA.fieldA=TableB.fieldA
join tableC on TableA.fieldA=TableC.fieldA
from tabelA join tableB on TableA.fieldA=TableB.fieldA
join tableC on TableA.fieldA=TableC.fieldA
因该再加一个distinct,否则就会又重复记录
table1 a,table2 b ,table3 c
where a.fielda=b.fielda(+) and a.fielda=c.fielda(+) and a.fielda=d.fielda(+)
create view view_all
as
select distinct FieldA from TableA
union
select distinct FieldA from TableB
union
select distinct FieldA from TableC
go然后用下面的语句:
select distinct a.FieldA ,b.FieldB,c.FieldC,d.FieldD
from view_all a
left join TableA b on a.FieldA = b.FieldA
left join TableB c on a.FieldA = c.FieldA
left join TableC d on a.FieldA = d.FieldA