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

Question

Tb1code number datetime
001 2 2012-3-2
001 5 2012-3-3
002 3 2012-3-4
001 5 2012-3-4Tb2
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

Answer 1

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'
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) 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 行受影响)
**/

Answer 2

但是如果下面的数据
Tb1code 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-1Tb2
code datetime
001 2012-3-3
002 2012-3-6
001 2012-3-2
003 2012-5-1得到下面结果呢?
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
004  6           NULL   

Answer 3

--> 测试数据：[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 ALL
SELECT '001',5,'2012-3-3' UNION ALL
SELECT '002',3,'2012-3-4' UNION ALL
SELECT '001',5,'2012-3-4' UNION ALL
SELECT '004',6,'2012-1-1'
GOIF 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 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) b
on a.[code]=b.[code]
group by a.[code],b.[code]
order by [code]
/*
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
004  6           NULL
警告: 聚合或其他 SET 操作消除了 Null 值。(4 行受影响)*/

Answer 4

--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]