create table #cc ( a char(2) ,b char(2) ,c char(2) ) create table #dd ( a char(2) ,b char(2) ,d char(2) )insert #cc select '10','11','33' union all select '13','15',nullinsert #dd select '10','11','34' union all select '20','30',nullselect * From #ddselect * From #cc select isnull(CC.A,DD.A),IsNull(CC.B,DD.B),CC.C,DD.D from #CC cc full outer join #DD dd on CC.A=DD.A and CC.B=DD.B drop table #cc drop table #dd
--应该这样处理:create table #CC(A int, B int, C int)insert into #CC select 10, 11, 33 insert into #CC select 13, 15, NULL create table #DD(A int, B int, D int)insert into #DD select 10, 11, 34 insert into #DD select 20, 30, NULL select * from #CC select * from #DD select t.A,t.B,t1.C,t2.D from ( select A,B from #CC union select A,B from #DD ) as t left join #CC as t1 on t.A=t1.A and t.B=t1.B left join #DD as t2 on t.A=t2.A and t.B=t2.Bdrop table #CC,#DD
SELECT A.a,A.b,A.c,B.d FROM cc A FULL OUTER JOIN dd B ON A.a=B.a and A.b=B.b 不知道对不对。
select X.a, X.b,X.c,Y.d from X left join Y on X.a=Y.a and X.b=Y.b union select Y.a, Y.b,X.c,Y.d from Y left join X on X.a=Y.a and X.b=Y.b
--full join就可以了create table #CC(A int, B int, C int) insert into #CC select 10, 11, 33 insert into #CC select 13, 15, NULL create table #DD(A int, B int, D int) insert into #DD select 10, 11, 34 insert into #DD select 20, 30, NULL select isnull(#cc.a,#dd.a) as A ,isnull(#cc.b,#dd.b) as B,#cc.c,#dd.d from #cc full join #dd on #cc.a=#dd.a and #cc.b=#dd.b/* A B c d ----------- ----------- ----------- ----------- 10 11 33 34 13 15 NULL NULL 20 30 NULL NULL */ drop table #cc,#dd
select s.a,s.b,s.c,t.d from A s left outer join B t on s.a=t.a and s.b=t.b union select tt.a,tt.b,ss.c,tt.d from A ss right outer join B tt on ss.a=tt.a and ss.b=tt.b这个可以 用2个外连接就可以得到了.我测试通过了.
select #tmpCC.A,#tmpCC.B,#tmpCC.C,#tmpDD.D from #tmpCC left join #tmpDD on #tmpCC.A=#tmpDD.A and #tmpCC.B=#tmpDD.B union select #tmpDD.A,#tmpDD.B,#tmpCC.C,#tmpDD.D from #tmpDD left join #tmpCC on #tmpDD.A=#tmpCC.A and #tmpDD.B=#tmpCC.B
2个外连接测试通过
select isnull(cc.A,dd.A) A, isnull(cc.B,dd.B) B,cc.C,dd.D from cc full join dd on cc.A = dd.A and cc.B = dd.B
create table cc(A int, B int, C int) insert into cc values(10, 11, 33 ) insert into cc values(13, 15, NULL) create table dd(A int, B int, D int) insert into dd values(10, 11, 34 ) insert into dd values(20, 30, NULL) goselect isnull(cc.A,dd.A) A, isnull(cc.B,dd.B) B,cc.C,dd.D from cc full join dd on cc.A = dd.A and cc.B = dd.B--drop table cc,dd/* A B C D ----------- ----------- ----------- ----------- 10 11 33 34 13 15 NULL NULL 20 30 NULL NULL(所影响的行数为 3 行) */
(
a char(2)
,b char(2)
,c char(2)
)
create table #dd
(
a char(2)
,b char(2)
,d char(2)
)insert #cc
select '10','11','33'
union all
select '13','15',nullinsert #dd
select '10','11','34'
union all
select '20','30',nullselect * From #ddselect * From #cc
select isnull(CC.A,DD.A),IsNull(CC.B,DD.B),CC.C,DD.D
from #CC cc
full outer join #DD dd on CC.A=DD.A and CC.B=DD.B drop table #cc
drop table #dd
insert into #CC select 13, 15, NULL create table #DD(A int, B int, D int)insert into #DD select 10, 11, 34
insert into #DD select 20, 30, NULL
select * from #CC
select * from #DD
select t.A,t.B,t1.C,t2.D
from
(
select A,B from #CC
union
select A,B from #DD
) as t
left join #CC as t1 on t.A=t1.A and t.B=t1.B
left join #DD as t2 on t.A=t2.A and t.B=t2.Bdrop table #CC,#DD
不知道对不对。
from X left join Y
on X.a=Y.a and X.b=Y.b
union
select Y.a, Y.b,X.c,Y.d
from Y left join X
on X.a=Y.a and X.b=Y.b
insert into #CC select 13, 15, NULL create table #DD(A int, B int, D int) insert into #DD select 10, 11, 34
insert into #DD select 20, 30, NULL
select isnull(#cc.a,#dd.a) as A ,isnull(#cc.b,#dd.b) as B,#cc.c,#dd.d
from #cc
full join #dd
on #cc.a=#dd.a and #cc.b=#dd.b/*
A B c d
----------- ----------- ----------- -----------
10 11 33 34
13 15 NULL NULL
20 30 NULL NULL
*/
drop table #cc,#dd
on s.a=t.a and s.b=t.b
union
select tt.a,tt.b,ss.c,tt.d from A ss right outer join B tt
on ss.a=tt.a and ss.b=tt.b这个可以 用2个外连接就可以得到了.我测试通过了.
from #tmpCC left join #tmpDD
on #tmpCC.A=#tmpDD.A and #tmpCC.B=#tmpDD.B
union
select #tmpDD.A,#tmpDD.B,#tmpCC.C,#tmpDD.D
from #tmpDD left join #tmpCC
on #tmpDD.A=#tmpCC.A and #tmpDD.B=#tmpCC.B
2个外连接测试通过
from cc full join dd on cc.A = dd.A and cc.B = dd.B
insert into cc values(10, 11, 33 )
insert into cc values(13, 15, NULL)
create table dd(A int, B int, D int)
insert into dd values(10, 11, 34 )
insert into dd values(20, 30, NULL)
goselect isnull(cc.A,dd.A) A, isnull(cc.B,dd.B) B,cc.C,dd.D
from cc full join dd on cc.A = dd.A and cc.B = dd.B--drop table cc,dd/*
A B C D
----------- ----------- ----------- -----------
10 11 33 34
13 15 NULL NULL
20 30 NULL NULL(所影响的行数为 3 行)
*/