create table #temp(id int) insert into #temp select 1 union all select 2 union all select 3 union all select 4 go --SQL: ;with cte as (select rowno=ROW_NUMBER() over(order by getdate()), * from #temp) select * from ( select top(select COUNT(1)/100+1 from #temp) rowno=ROW_NUMBER() over(order by getdate()) from sys.columns a, sys.columns b ) a cross apply (select cnt=SUM(id) from cte where rowno between 100*(a.rowno-1) and (a.rowno*100-1)) b /* rowno cnt 1 10 */
from
(select rn=row_number() over(order by getdate()),value from tb) t
group by
(rn-1)/100
10 1
20 2
10 3
12 4
12 5
13 6
12 7
12 8
16 9
45 10
类似上面的数据结构,求A的1到5项数据的和,再求6到10项数据的和,在这里只是打个比方,实际数据有好几千行
insert into #temp
select 1 union all select 2 union all
select 3 union all select 4
go
--SQL:
;with cte as
(select rowno=ROW_NUMBER() over(order by getdate()), * from #temp)
select * from
(
select top(select COUNT(1)/100+1 from #temp)
rowno=ROW_NUMBER() over(order by getdate())
from sys.columns a, sys.columns b
) a
cross apply
(select cnt=SUM(id) from cte where rowno between 100*(a.rowno-1) and (a.rowno*100-1)) b
/*
rowno cnt
1 10
*/