日期之间的数量的统计

[dxinyan]

Tb1:

id date_time number
01 2012-5-1 100
01 2012-5-5 300
01 2012-5-6 200
01 2012-6-1 200
01 2012-6-2 600

Tb2:

id date_time1
01 2012-5-3
01 2012-5-7
01 2012-6-5

以Tb2的日期为准统计数量,比如2012-5-3之前在Tb1中01的数量和
2012-5-3和2012-5-7之间Tb1中在这个日期之间的和
2012-5-7和2012-6-5之间Tb1中在这个日期的和

统计如下结果
id date_time2 number
01 2012-5-3 100
01 2012-5-7 500
01 2012-6-5 800
怎么实现上面的统计?

[哥眼神纯洁不]

with a(id,date_time,number)as(
select '01','2012-5-1',100 union
select '01','2012-5-5',300 union
select '01','2012-5-6',200 union
select '01','2012-6-1',200 union
select '01','2012-6-2',600 )
,b(id,date_time1)as(
select '01','2012-5-3' union
select '01','2012-5-7' union
select '01','2012-6-5')
select id,date,SUM(number) from (
select id,date=(select MIN(date_time1) from b 
where a.id=b.id and a.date_time<b.date_time1),number from a
)a group by id,date

[lzw_0736]

IF OBJECT_ID('tempdb..#cu1') IS NOT NULL
	DROP TABLE #cu1;
IF OBJECT_ID('tempdb..#cu2') IS NOT NULL
	DROP TABLE #cu2;

WITH a1 (id,date_time,number) AS
(
SELECT '01','2012-5-1',100 UNION  all
SELECT '01','2012-5-5',300 UNION  all
SELECT '01','2012-5-6',200 UNION  all
SELECT '01','2012-6-1',200 UNION  all
SELECT '01','2012-6-2',600
)
SELECT * 
INTO #cu1
FROM a1
;
WITH a2 (id,date_time1) AS 
(
SELECT '01','2012-5-3' UNION  all
SELECT '01','2012-5-7' UNION  all
SELECT '01','2012-6-5'
)
,a3 AS
(
SELECT *,ROW_NUMBER() OVER (ORDER BY date_time1) re
FROM a2
)
,a4 AS
(
SELECT *,(SELECT date_time1 FROM a3 WHERE re=a.re-1) date_time0
FROM a3 a
)
SELECT *,
CASE WHEN date_time0 IS NULL 
THEN 'date_time<='''+date_time1+''''
else 'date_time<='''+date_time1+''' and date_time>'''+date_time0+''''
END date_tj
INTO #cu2
FROM a4

DECLARE @i1 INT,@i2 INT,@str VARCHAR(MAX)
SELECT @i1=1,@i2=MAX(re),@str='' FROM #cu2
WHILE @i1<=@i2
BEGIN
	SELECT @str=@str+CASE WHEN @str='' THEN '' ELSE ' union all ' END+
	'SELECT id,'''+date_time1+''' date_time2'+
	',SUM(number) number FROM #cu1 WHERE id='''+id+''' AND '+
	date_tj+' group by id'
	FROM #cu2 WHERE re=@i1
	SET @i1=@i1+1
END
EXEC(@str)

[Shawn]

with a(id,date_time,number)as(
select '01','2012-5-1',100 union
select '01','2012-5-5',300 union
select '01','2012-5-6',200 union
select '01','2012-6-1',200 union
select '01','2012-6-2',600 )
,b(id,date_time1)as(
select '01','2012-5-3' union
select '01','2012-5-7' union
select '01','2012-6-5'),
cte1 AS
(
	SELECT rowid=ROW_NUMBER() OVER(PARTITION BY id ORDER BY date_time1),*
	FROM b
),
cte2 AS
(
	SELECT a.rowid,a.id,endtime=a.date_time1,begintime=b.date_time1
	FROM cte1 a
	LEFT JOIN cte1 b
	ON a.rowid = b.rowid+1
)
SELECT M.ID, date_time2=M.endtime, n.number FROM cte2 m
OUTER APPLY
(SELECT number =SUM(number) FROM a WHERE a.id = m.id AND date_time <= m.endtime AND date_time > ISNULL(m.begintime, '19000101')) n
/*
ID	date_time2	number
01	2012-5-3		100
01	2012-5-7		500
01	2012-6-5		800
*/