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

有三张表tab1/tab2/tab3结构如下:

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

要求合并三张表,created相同的并为一行,新表tab4结构如下:

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

说明:相比之下,第三种方法简单也容易理解。

评论: 0 | 引用: 0 | 查看次数: 7815
发表评论
登录后再发表评论!