求助：经典SQL面试验题,在线等答案 急！！！

[woshipizi]

/*
题目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表中的两条
请高手帮忙解决

[简若的学习窝]

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

[playwarcraft]

--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

[zanyzyg]

对啊，就是个全连接吗！

[OracleRoob]

--这样?

select CC.A,CC.B,CC.C,DD.D
from CC
left join DD on CC.A=DD.A and CC.B=DD.B

[mustudent]

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

[OracleRoob]

--应该这样处理：



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

[yooooyiiiiiiiiii]

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
不知道对不对。

[yooooyiiiiiiiiii]

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

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

[cs_oldhorse]

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

[xiaoku]

路过接分...

[dawugui]

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

[dawugui]

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)
go

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

--drop table cc,dd

/*
A B C D
----------- ----------- ----------- -----------
10 11 33 34
13 15 NULL NULL
20 30 NULL NULL

（所影响的行数为 3 行）
*/

[yingzi5839]

2楼的就对

[woshipizi]

非常感谢大家的指教

[woshipizi]

我没有太多得分大家分给的少大家都有
辛苦了