--Like this??declare @T1 table
(
F1 int,
F2 int
)
insert into @T1
select 1,10 union all
select 2,20
declare @T2 table
(
F1 int,
F3 int
)
insert into @T2
select 2,25 union all
select 3,21
declare @T3 table
(
F1 int,
F4 int
)
insert into @T3
select 1,22 union all
select 3,21 union all
select 4,40select isnull(isnull(a.F1,b.F1),c.F1) as F1,sum(F2) as F2,sum(F3) as F3,sum(F4)as F4
from
@T1 a
full join
@T2 b
on
a.F1=b.F1
full join
@T3 c
on
a.F1=c.F1
Group by
isnull(isnull(a.F1,b.F1),c.F1)/*
F1 F2 F3 F4
----------- ----------- ----------- -----------
1 10 NULL 22
2 20 25 NULL
3 NULL 21 21
4 NULL NULL 40*/
(
F1 int,
F2 int
)
insert into @T1
select 1,10 union all
select 2,20
declare @T2 table
(
F1 int,
F3 int
)
insert into @T2
select 2,25 union all
select 3,21
declare @T3 table
(
F1 int,
F4 int
)
insert into @T3
select 1,22 union all
select 3,21 union all
select 4,40select isnull(isnull(a.F1,b.F1),c.F1) as F1,sum(F2) as F2,sum(F3) as F3,sum(F4)as F4
from
@T1 a
full join
@T2 b
on
a.F1=b.F1
full join
@T3 c
on
a.F1=c.F1
Group by
isnull(isnull(a.F1,b.F1),c.F1)/*
F1 F2 F3 F4
----------- ----------- ----------- -----------
1 10 NULL 22
2 20 25 NULL
3 NULL 21 21
4 NULL NULL 40*/
--表之间关系的对应关系,如果猜想写不一定满足LZ要求。
create table t1 (f1 int,f2 int)
create table t2(f1 int,f3 int)
create table t3(f1 int,f4 int)
insert into t1 select 1,2
insert into t1 select 2,3
insert into t1 select 3,4
insert into t2 select 1,5
insert into t2 select 2,6
insert into t3 select 1,7
insert into t3 select 2,8
insert into t3 select 3,9/**/
select A.f1,sum(A.f2) as f2,sum(A.f3) as f3,sum(A.f4) as f4
from
(select f1,f2,0 as f3,0 as f4 from t1
union all
select f1,0,f3,0 from t2
union all
select f1,0,0,f4 from t3) A
group by A.f1/*The result*/
f1 f2 f3 f4
----------- ----------- ----------- -----------
1 2 5 7
2 3 6 8
3 4 0 9