数据统计
我现在遇到一个问题，要统计一个表中三日连续累加数据。如上表
表名   Temp
字段1   datetime
字段2   real
数据：
2006-01-01   0.3
2006-01-02   0.4
2006-01-03   1
2006-01-05   3
2006-01-06   7
2006-01-08   2
2006-01-09   4
2006-01-10   7
找出三日连续数据，结果为
2006-01-01   1.7
2006-01-08   13



------解决方案--------------------
select * from real as a
where exists(select 1 from real where DATEDIFF(day,[datetime],a.[datetime])!=1)
and
exists(select 1 from real where DATEDIFF(day,a.[datetime],[datetime])!=1)
------解决方案--------------------
create table T(col1 datetime, col2 decimal(10,2))
insert T select '2006-01-01 ', 0.3
union all select '2006-01-02 ', 0.4
union all select '2006-01-03 ', 1

union all select '2006-01-05 ', 3
union all select '2006-01-06 ', 7

union all select '2006-01-08 ', 2
union all select '2006-01-09 ', 4
union all select '2006-01-10 ', 7


select betDate=a.col1, endDate=min(b.col1),
col2=(select sum(col2) from T where col1 between a.col1 and min(b.col1))
from
(
select * from T tmp
where not exists(select 1 from T where datediff(day, tmp.col1, col1)=-1)
)a,
(
select * from T tmp
where not exists(select 1 from T where datediff(day, tmp.col1, col1)=1)
)b
where a.col1 <=b.col1
group by a.col1, a.col2
having datediff(day, a.col1, min(b.col1))> 1

--result
betDate endDate col2
------------------------------------------------------ ------------------------------------------------------ ----------------------------------------
2006-01-01 00:00:00.000 2006-01-03 00:00:00.000 1.70
2006-01-08 00:00:00.000 2006-01-10 00:00:00.000 13.00

(2 row(s) affected)