if object_id('[Tb1]') is not null drop table [Tb1]
go
create table [Tb1]([code] varchar(3),[number] int,[datetime] datetime)
insert [Tb1]
select '001',2,'2012-3-2' union all
select '001',5,'2012-3-3' union all
select '002',3,'2012-3-4' union all
select '001',5,'2012-3-4'
go
if object_id('[Tb2]') is not null drop table [Tb2]
go
create table [Tb2]([code] varchar(3),[datetime] datetime)
insert [Tb2]
select '001','2012-3-3' union all
select '002','2012-3-6' union all
select '001','2012-3-2' union all
select '003','2012-5-1'
go

select b.code,
  sum(case when a.[datetime]>=b.[datetime] then a.number else 0 end) as number,
  b.[datetime]
from (select code,max([datetime]) as [datetime] from tb2 group by code) b
left join tb1 a on a.code=b.code
group by b.code,b.[datetime]

/**
code number      datetime
---- ----------- -----------------------
001  10          2012-03-03 00:00:00.000
002  0           2012-03-06 00:00:00.000
003  0           2012-05-01 00:00:00.000

(3 行受影响)
**/

------解决方案--------------------

探讨


但是如果下面的数据
Tb1

code number datetime
001 2 2012-3-2
001 5 2012-3-3
002 3 2012-3-4
001 5 2012-3-4
004 6 2012-1-1

Tb2
code datetime
001 2012-3-3
002 2012-3-6
001 2012-3-2
003 2012-5-1

……


------解决方案--------------------
SQL code
--try
select
 b.code,
  sum(case when a.[datetime]>=b.[datetime] then a.number else 0 end) as number,
  b.[datetime]
from
 (select code,max([datetime]) as [datetime] from tb2 group by code) b
full join
 tb1 a on a.code=b.code
group by
 b.code,b.[datetime]