SQL Server多张表数据合并几个思路

create table tab1
(
	f1 int,
	created date
)
create table tab2
(
	f2 int,
	created date
)
create table tab3
(
	f3 int,
	created date
)

create table tab4
(
	f1 int,
	f2 int,
	f3 int,
	created date
)

--合并tab1和tab2到#t1
select
	ISNULL(tab1.f1,0) f1,
	ISNULL(tab2.f2,0) f2,
	ISNULL(tab1.created, tab2.created) created
into #t1
from tab1 full join tab2 on tab1.created = tab2.created
--合并#t1和tab3到#t2
select
	ISNULL(#t1.f1,0) f1,
	ISNULL(#t1.f2,0) f2,
	ISNULL(tab3.f3,0) f3,
	ISNULL(#t1.created, tab3.created) created
into #t2
from #t1 full join tab3 on #t1.created = tab3.created
--复制数据
insert into tab4(f1,f2,f3,created) select f1,f2,f3,created from #t2

insert into tab4(f1,f2,f3,created)
select
	ISNULL(tab1.f1,0) f1,
	ISNULL(tab2.f2,0) f2,
	ISNULL(tab3.f3,0) f3,
	ISNULL(tab1.created, ISNULL(tab2.created, tab3.created)) created
from tab1
full join tab2 on tab1.created = tab2.created
full join tab3 on tab1.created = tab3.created or tab2.created = tab3.created

--创建临时表:结构和tab4一样
create table #t1
(
	f1 int,
	f2 int,
	f3 int,
	created smalldatetime
)
--合并数据
insert into #t1(f1,f2,f3,created) select f1,0,0,created from tab1
insert into #t1(f1,f2,f3,created) select 0,f2,0,created from tab2
insert into #t1(f1,f2,f3,created) select 0,0,f3,created from tab3
--分组得结果
insert into tab4(f1,f2,f3,created) select SUM(f1) f1,SUM(f2) f2,SUM(f3) f3,created from #t1 group by created