如何写这个sql语句

[fstao]

有两个表：
表#t1数据如下：
a_id b_id c_id price0
1 null 2 10
1 null 3 20
2 1 2 10


表#t2数据如下：
a_id b_id c_id price1 price2
1 null 2 100 110
2 1 2 200 220


我想以#t1显示#t2.price1和#t2.price2，条件是：#t1.a_id=#t2.a_id and #t1.b_id=#t2.b_id and #t1.c_id=#t2.c_id
则#t1显示如下：
a_id b_id c_id price0 price1 price2
1 null 2 10 100 110
1 null 3 20
2 1 2 10 200 220

请问这个sql语句如何来写？

[--小F--]

select
   a.*,
   isnull(b.price1,'') as price1,
   isnull(b.price2,'') as price1
from
   #t1 a
full join
   #t2 b
on
   a.a_id=b.a_id and a.b_id=b.b_id and a.c_id=b.c_id

[--小F--]

select
   isnull(a.a_id,b.a_id) as a_id,
   isnull(a.b_id,b.b_id) as b_id,
   isnull(a.c_id,b.c_id) as c_id,
   isnull(b.price1,'') as price1,
   isnull(b.price2,'') as price1
from
   #t1 a
full join
   #t2 b
on
   a.a_id=b.a_id and a.b_id=b.b_id and a.c_id=b.c_id

[cd731107]

select a.a_id,a.b_id,a.c_id,a.price0,b.price1,b.price2
from #t1 a left join #t2 b
on 
a.a_id=b.a_id and isnull(a.b_id,0)=isnull(b.b_id,0) and a.c_id=b.c_id

[fstao]

数据显示还是错误变成：
a_id b_id c_id price0 price1 price2
1 NULL 2 10.0000 NULL NULL
1 NULL 3 20.0000 NULL NULL
2 1 2 10.0000 200.0000 220.0000
NULL NULL NULL NULL 100.0000 110.0000

但是我显示成：
a_id b_id c_id price0 price1 price2
1 null 2 10 100 110
1 null 3 20
2 1 2 10 200 220

[fstao]

这样写的代码不行的：
create table #t1(a_id int,b_id int,c_id int,price0 decimal(18,4))

create table #t2(a_id int,b_id int,c_id int,price1 decimal(18,4),price2 decimal(18,4))

insert into #t1(a_id,b_id,c_id,price0)
select 1,null,2,10
union all
select 1,null,3,20
union all
select 2,1,2,10

insert into #t2(a_id,b_id,c_id,price1,price2)
select 1,null,2,100,110
union all
select 2,1,2,200,220

select * from #t1
select * from #t2

select
a.*,
b.price1,
b.price2
from
#t1 a
full join
#t2 b
on
a.a_id=b.a_id and a.b_id=b.b_id and a.c_id=b.c_id

drop table #t1,#t2

[cd731107]

[Quote=引用 3 楼 cd731107 的回复:]
SQL code
select a.a_id,a.b_id,a.c_id,a.price0,b.price1,b.price2
from #t1 a left join #t2 b
on
a.a_id=b.a_id and isnull(a.b_id,0)=isnull(b.b_id,0) and a.c_id=b.c_id
[/Quote]
和你要的结果一样的吧

[cd731107]

a_id b_id c_id price0 price1 price2
----------- ----------- ----------- -------------------- ------------ --
1 NULL 2 10.0000 100.0000 110.0000
1 NULL 3 20.0000 NULL NULL
2 1 2 10.0000 200.0000 220.0000

[--小F--]

full join 换left join

select
isnull(a.a_id,b.a_id) as a_id,
isnull(a.b_id,b.b_id) as b_id,
isnull(a.c_id,b.c_id) as c_id,
isnull(b.price1,'') as price1,
isnull(b.price2,'') as price1
from
#t1 a
left join
#t2 b
on
a.a_id=b.a_id and a.b_id=b.b_id and a.c_id=b.c_id