t1 t2 t3
a b c a b d a b e
------- ------- -------
1 a x 3 b x 3 b x
2 b x 4 d x 5 b x
3 b x 5 b x 6 d x我想要
t
a b c d e
-------------
1 a x null null
2 b x null null
3 b x x x
4 d x null null
5 b null x x
6 d x null null即:a,b为相关字段,相同合并,不同为空
能构造出来么(函数或者存储过程或者视图皆可,能用sql实现就行)?先谢谢了…
a b c a b d a b e
------- ------- -------
1 a x 3 b x 3 b x
2 b x 4 d x 5 b x
3 b x 5 b x 6 d x我想要
t
a b c d e
-------------
1 a x null null
2 b x null null
3 b x x x
4 d x null null
5 b null x x
6 d x null null即:a,b为相关字段,相同合并,不同为空
能构造出来么(函数或者存储过程或者视图皆可,能用sql实现就行)?先谢谢了…
IsNull(A.a, D.a) As a,
IsNull(A.b, D.b) As b,
A.c,
D.d,
D.e
From
A
Full Join
(Select
IsNull(B.a, C.a) As a,
IsNull(B.b, C.b) As b,
B.d,
C.e
From
B
Full Join
C
On B.a = C.a And B.b = C.b) D
On A.a = D.a And A.b = D.b
(select a,b from t1 union select * from t2 union select * from t3)a
left join t1 b
on b.a=a.a
left join t2 c
on c.a=a.a
left join t3 d
on d.a=a.a
insert into t1 select 1, 'a', 'x'
union all select 2, 'b', 'x'
union all select 3, 'b', 'x'
create table t2(a int, b varchar(5), d varchar(5))
insert into t2 select 3, 'b', 'x'
union all select 4, 'd', 'x'
union all select 5, 'b', 'x' create table t3(a int, b varchar(5), e varchar(5))
insert into t3 select 3, 'b', 'x'
union all select 5, 'b', 'x'
union all select 6, 'd', 'x'select a.*,c,d,e from
(select a,b from t1 union select a,b from t2 union select a,b from t3)a
left join t1 b
on b.a=a.a
left join t2 c
on c.a=a.a
left join t3 d
on d.a=a.a
--是楼主写错啦吗??
a b c d e
----------- ----- ----- ----- -----
1 a x NULL NULL
2 b x NULL NULL
3 b x x x
4 d NULL x NULL
5 b NULL x x
6 d NULL NULL x
select a.*,c,d,e from
(select a,b from t1 union select a,b from t2 union select a,b from t3)a
left join t1 b
on b.a=a.a
left join t2 c
on c.a=a.a
left join t3 d
on d.a=a.a