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
------解决方案--------------------
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