关于取出2个表的，信息 做操作，插到第3个表，求教
是从一表A里取，一个id,一个date，一个数量(count)
在从另一个表B里取一个，时间集合（date）
假设表B 的数据是
abc 20100501 20
abc 20100507 120
abc 20100505 30
dbd 20100510 50
假设表B 的数据是
20100505
20100506
20100509
现在要根据AB表date 比较插入C 表
C表
插入完是
abc 20100505 20
abc 20100505 30
abc 20100506 120
abc 20100505 0
abc 20100506 0
abc 20100509 0
dbd 20100509 50
dbd 20100505 0
dbd 20100506 0
dbd 20100509 0
逻辑要求 取B的的所有date 都按 数量插入一般， 并且 A的date 如果在B表里有就直接插入，没有就插入他以上的最近的日期，没有以上的就插入以下最近的。
[解决办法]
--> 测试数据：[tbla]goif object_id('[tbla]') is not null drop table [tbla]gocreate table [tbla]([name] varchar(3),[date] datetime,[num] int)goinsert [tbla]select 'abc','20100501',20 union allselect 'abc','20100507',120 union allselect 'abc','20100505',30 union allselect 'dbd','20100510',50--> 测试数据：[tblb]goif object_id('[tblb]') is not null drop table [tblb]gocreate table [tblb]([date] datetime)goinsert [tblb]select '20100505' union allselect '20100506' union allselect '20100509'create table #t([name] varchar(3),[date] datetime,[num] int)goinsert #tselect 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] across join [tblb] b)c)d where id=1union allselect 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) ccross join(select ROW_NUMBER()over(order by getdate()) as id,[date],num=0 from tblb) d) e where id=1select * from #t order by name,num desc/*name date numabc 2010-05-06 00:00:00.000 120abc 2010-05-05 00:00:00.000 30abc 2010-05-05 00:00:00.000 20abc 2010-05-05 00:00:00.000 0abc 2010-05-06 00:00:00.000 0abc 2010-05-09 00:00:00.000 0dbd 2010-05-09 00:00:00.000 50dbd 2010-05-05 00:00:00.000 0dbd 2010-05-06 00:00:00.000 0dbd 2010-05-09 00:00:00.000 0*/