日期:2014-05-17 浏览次数:20419 次
create table #ta(col1 varchar(10),col2 varchar(10))
insert into #ta(col1,col2)
select 'a',null
union all select 'b',8
union all select 'c',null
create table #tb(col1 varchar(10),col2 varchar(10),col3 varchar(10))
insert into #tb(col1,col2,col3)
select 'a',1,null
union all select 'b',1,null
union all select 'c',2,20
go
select t1.col1,t1.bcol2 as col2,case when t2.col3 is not null then t2.col3 else t1.col2 end as col3
from
(
select a.col1,b.col2 as bcol2,a.col2
from #ta a,(select distinct col2 from #tb)b
)t1
left join #tb t2 on t1.col1=t2.col1
drop table #ta,#tb
/*
col1 col2 col3
a 1 NULL
b 1 8
c 1 20
a 2 NULL
b 2 8
c 2 20
*/