联合查询.txt

来自「SQL语言常用的一些命令各代码」· 文本 代码 · 共 38 行

A:
A B C D
1 1 1 a
2 2 2 b
3 3 3 c
表B:
A B C E
1 1 1 x
2 2 2 y
4 4 4 z
现在在想通过查询得到
A B C D E
1 1 1 a x
2 2 2 b y
3 3 3 c
4 4 4 z

/*解答*/
declare @a table(a int,b int,c int,d char(1))
insert into @a select 1,1,1,'a'
union all      select 2,2,2,'b'
union all      select 3,3,3,'c'
declare @b table (a int,b int,c int ,e char(1))
insert into @b select 1,1,1,'x'
union all      select 2,2,2,'y'
union all      select 4,4,4,'z'
/*查询1*/
select distinct * from (
(select t.a,t.b,t.c,t.d ,isnull(y.e,'')as e
from @a t
left join @b y
on t.a=y.a and t.b=y.b and t.c=y.c)
union (select l.a,l.b,l.c,isnull(n.d,'')as d,l.e 
from @b l
left join @a n
on l.a=n.a and l.b=n.b and l.c=n.c))xx
/*查询2*/
select isnull(t.a,y.a),isnull(t.b,y.a),isnull(t.c,y.c),isnull(t.d,''), isnull(y.e,'') from  @a t
full join @b y
on t.a=y.a and t.b=y.b and t.c=y.c
order by isnull(t.a,y.a)