请教一个表中最近一天在另一个表中的和
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 '003',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 all
select '002','2012-3-6' union all
select '001','2012-3-2'
--------------开始查询--------------------------
select
a.code,sum(a.number) as number
from
tb1 a left join tb2 b
on
a.code=b.code
where
b.datetime=(select max(datetime) from tb2 where code=b.code)
and
a.datetime>=b.datetime
group by
a.code

最后得到结果:
001 5
003 0

[解决办法]

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 行受影响)**/
[解决办法]
为什么发两贴

SQL code
--> 测试数据：[Tb1]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' UNION ALLSELECT '004',6,'2012-1-1'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'GO--> 测试语句：SELECT isnull(a.[code],b.[code]) as [code],sum(case when  a.[datetime]>=isnull(b.[datetime],0) then a.number else 0 end) as number,max(b.[datetime]) as [datetime]FROM [Tb1] a full join (select code,max([datetime]) as [datetime] from tb2 group by code) bon a.[code]=b.[code]group by a.[code],b.[code]order by [code]/*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.000004  6           NULL警告: 聚合或其他 SET 操作消除了 Null 值。(4 行受影响)*/
[解决办法]
SQL code
SELECT  a.code,sum(case when  a.[datetime]>=isnull(b.[datetime],0) then a.number else 0 end) as number, b.[datetime]  FROM [Tb1] a left join (select code,max([datetime]) as [datetime] from tb2 group by code) bon a.[code]=b.[code]group by a.[code],b.datetimeorder by [code]