--> 测试数据：[tbla]
go
if object_id('[tbla]') is not null 
drop table [tbla]
go
create table [tbla](
[name] varchar(3),
[date] datetime,
[num] int
)
go
insert [tbla]
select 'abc','20100501',20 union all
select 'abc','20100507',120 union all
select 'abc','20100505',30 union all
select 'dbd','20100510',50
--> 测试数据：[tblb]
go
if object_id('[tblb]') is not null 
drop table [tblb]
go
create table [tblb](
[date] datetime
)
go
insert [tblb]
select '20100505' union all
select '20100506' union all
select '20100509'

create table #t(
[name] varchar(3),
[date] datetime,
[num] int
)
go
insert #t
select name,bdate,num from (
select ROW_NUMBER()over(partition by name,[date] 
order by abs(datediff(dd,[date],bdate)) asc) as id,* from(
select a.name,a.[date],
b.[date] as bdate,a.num  from [tbla] a
cross join [tblb] b)c
)d where id=1
union all
select name,[date],num from(
select ROW_NUMBER()over(partition by name,d.[date] order by d.[date]) as id,
c.name,d.[date],isnull(d.num,0) as num from
(select ROW_NUMBER()over(partition by name order by getdate()) as id,*from tbla) c
cross join(
select ROW_NUMBER()over(order by getdate()) as id,[date],num=0 from tblb) d
) e where id=1

select * from #t order by name,num desc

/*
name    date    num
abc    2010-05-06 00:00:00.000    120
abc    2010-05-05 00:00:00.000    30
abc    2010-05-05 00:00:00.000    20
abc    2010-05-05 00:00:00.000    0
abc    2010-05-06 00:00:00.000    0
abc    2010-05-09 00:00:00.000    0
dbd    2010-05-09 00:00:00.000    50
dbd    2010-05-05 00:00:00.000    0
dbd    2010-05-06 00:00:00.000    0
dbd    2010-05-09 00:00:00.000    0
*/