以下表按照日期升序排序,但日期不一定连续,我想现在这样来求和,就是分别间隔3条和间隔5条记录进行分组求和 theDate price
2011-10-01 5
2011-10-03 3
2011-10-06 3
2011-10-09 2
2011-10-10 6
2011-10-11 2
2011-10-15 9
2011-10-18 5
3条记录间隔分组,希望得到的结果filed1 filed2
1 11
2 10
3 145条记录间隔分组,希望得到的结果filed1 filed2
1 19
2 16
2011-10-01 5
2011-10-03 3
2011-10-06 3
2011-10-09 2
2011-10-10 6
2011-10-11 2
2011-10-15 9
2011-10-18 5
3条记录间隔分组,希望得到的结果filed1 filed2
1 11
2 10
3 145条记录间隔分组,希望得到的结果filed1 filed2
1 19
2 16
select (rid-1)/3 as filed1,sum(price) filed2
from (
select *,rid=row_number() over (order by theDate)
from tb
)t
group by (rid-1)/3 --3是间隔数
(select row_number() over(order by theDate) no,price from tb) t
group by (no-1)%3--把3改为5即可得到5的
--修改
select (no-1)/3+1,sum(price) from
(select row_number() over(order by theDate) no,price from tb) t
group by (no-1)/3--把3改为5即可得到5的
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (theDate datetime,price int)
insert into [tb]
select '2011-10-01',5 union all
select '2011-10-03',3 union all
select '2011-10-06',3 union all
select '2011-10-09',2 union all
select '2011-10-10',6 union all
select '2011-10-11',2 union all
select '2011-10-15',9 union all
select '2011-10-18',5--开始查询
;with cte as(
select rid=ROW_NUMBER() over (order by theDate),* from [tb]
)
select filed1=(rid-1)/3+1,filed2=SUM(price) from cte
group by (rid-1)/3--结束查询
drop table [tb]/*
filed1 filed2
-------------------- -----------
1 11
2 10
3 14(3 行受影响)