查询出code中的产品代码在 Tb2中新近的一天以后,在Tb1的总和

查询出code中的产品代码在 Tb2中最近的一天以后,在Tb1的总和
Tb1

code number datetime
001 2 2012-3-2
001 5 2012-3-3
002 3 2012-3-4
001 5 2012-3-4

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

查询出code中的产品代码在 Tb2中最近的一天以后,在Tb1的总和,如果在T1中没有,也要显示出来,最终得到下面结果:
code number datetime(最近的日期)
001 10 2012-3-3
002 0 2012-3-6
003 0 2012-5-1


[解决办法]

SQL code

if object_id('[Tb1]') is not null drop table [Tb1]gocreate table [Tb1]([code] varchar(3),[number] int,[datetime] datetime)insert [Tb1]select '001',2,'2012-3-2' union allselect '001',5,'2012-3-3' union allselect '002',3,'2012-3-4' union allselect '001',5,'2012-3-4'goif object_id('[Tb2]') is not null drop table [Tb2]gocreate table [Tb2]([code] varchar(3),[datetime] datetime)insert [Tb2]select '001','2012-3-3' union allselect '002','2012-3-6' union allselect '001','2012-3-2' union allselect '003','2012-5-1'goselect 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) bleft join tb1 a on a.code=b.codegroup by b.code,b.[datetime]/**code number      datetime---- ----------- -----------------------001  10          2012-03-03 00:00:00.000002  0           2012-03-06 00:00:00.000003  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
--tryselect 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) bfull join tb1 a on a.code=b.codegroup by b.code,b.[datetime]