请问一个表中最近一天在另一个表中的合

请教一个表中最近一天在另一个表中的合
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

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




[解决办法]

SQL code

--这样吗？------------------------------ Author  :fredrickhu(小F，向高手学习)-- Date    :2012-07-17 14:56:27-- Version:--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) --    Apr 22 2011 11:57:00 --    Copyright (c) Microsoft Corporation--    Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[Tb1]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 allselect '001',5,'2012-3-3' union allselect '002',3,'2012-3-4' union allselect '001',5,'2012-3-4'--> 测试数据：[Tb2]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 allselect '002','2012-3-6' union allselect '001','2012-3-2'--------------开始查询--------------------------select  a.code,sum(a.number) as numberfrom  tb1 a join tb2 bon  a.code=b.code where  b.datetime=(select max(datetime) from tb2 where code=b.code)and  a.datetime>b.datetimegroup by   a.code----------------结果----------------------------/* code number---- -----------001  5(1 行受影响)*/