求助：经典SQL面试验题,在线等答案 急！！！
/* 
题目1： 
有cc、dd两张表A、B是组合主键 
cc表 
A B C 
10 11 33 
13 15 NULL 
dd表 
A B D 
10 11 34 
20 30 NULL 
怎么才能查出如下结果： 
A B C D 
10 11 33 34 
13 15 NULL NULL 
20 30 NULL NULL 
要求：写出T－SQL语句，不能通过存储过程等！ 
select a,b,c,d  
from cc left join dd 
on cc.a=dd.a 
用上面的语句显示错误，不能确定a和b 是哪个表的 
如果变成 
select cc.a,cc.b,c,d  
from cc left join dd 
on cc.a=dd.a 
只能显示cc表中的两条 
请高手帮忙解决

------解决方案--------------------
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',null

insert #dd
select '10','11','34'
union all 
select '20','30',null

select * From #dd

select * 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.B

drop table #CC,#DD

------解决方案--------------------
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

这个可以　用２个外连接就可以得到了．我测试通过了．

------解决方案--------------------
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个外连接测试通过
------解决方案--------------------