时间段分组统计
表A
sysdatetime value
2007-09-03 100
2007-09-04 20
......
2007-09-20 300
如何显示
时间段 总数
2003-09-03 至 2007-9-05 500
2003-09-06 至 2007-9-08 600
......
[解决办法]
declare @a table(a varchar(10),value int)
insert @a select '2007-09-03 ', 100
union all select '2007-09-04 ', 20
union all select '2007-09-05 ', 20
union all select '2007-09-06 ', 20
union all select '2007-09-07 ', 20
union all select '2007-09-08 ', 20
union all select '2007-09-09 ', 20
union all select '2007-09-10 ', 20
union all select '2007-09-11 ', 20
union all select '2007-09-12 ', 20
union all select '2007-09-13 ', 20
union all select '2007-09-14 ', 20
union all select '2007-09-15 ', 20
union all select '2007-09-16 ', 20
union all select '2007-09-17 ', 20
union all select '2007-09-18 ', 20
union all select '2007-09-19 ', 20
union all select '2007-09-20 ', 300
select b.a+ ' 至 '+convert(varchar(10),b.c, 120) 时间段,sum(case when a.a between.b.a and b.c then a.value else 0 end) 总数 from @a a,(
select b.*,dateadd(day,2,b.a) c from (select min(a) a,max(a) b from @a) a,@a b where datediff(day,a.a,b.a)*1.0/3=cast(datediff(day,a.a,b.a)/3 as int)
)b
group by b.a,b.c
--result
/*
时间段 总数
------------------------ -----------
2007-09-03 至 2007-09-05 140
2007-09-06 至 2007-09-08 60
2007-09-09 至 2007-09-11 60
2007-09-12 至 2007-09-14 60
2007-09-15 至 2007-09-17 60
2007-09-18 至 2007-09-20 340
*/
