以下代碼在SQL 2000下測試通過。 /*程式里是每2筆加一次,可根據情況把@idx=2改成想要的筆數*/ /* temp table #result存的是結果,第一筆為1~2的加總,第二筆為3~4的加總……*/ Declare @p table (id int ,fld int) declare @cnt int declare @pct int declare @idx intset @cnt=0 set @pct=0 set @idx=0insert into @p values (1,1) insert into @p values (2,3) insert into @p values (3,5) insert into @p values (4,7) insert into @p values (5,10) insert into @p values (6,11) insert into @p values (7,30) insert into @p values (8,133)create table #result (result int)declare abc Cursor for select fld from @p order by id open abc Fetch next from abc into @pct while @@Fetch_status=0 begin SET @cnt=@cnt+@pct set @idx=@idx+1 IF @idx=2 Begin insert into #Result(Result) values (@cnt) set @idx=0 set @cnt=0 end fetch next from abc into @pct end select fld from @p select * from #Result drop table #Result close abc deallocate abc
加个标志字段(1,2,3,4...),用group by实现
如果你的字段1是自动增长且中间无断行 select sum(字段2) from testtable group by(字段1/10)
如果你是在SQLSERVER中 select ID=identity(int ,1,1),* into #TempTable from OldTable order by field1select id/10,sum(field2) from #TempTable group by id/10
/*程式里是每2筆加一次,可根據情況把@idx=2改成想要的筆數*/
/* temp table #result存的是結果,第一筆為1~2的加總,第二筆為3~4的加總……*/
Declare @p table (id int ,fld int)
declare @cnt int
declare @pct int
declare @idx intset @cnt=0
set @pct=0
set @idx=0insert into @p values (1,1)
insert into @p values (2,3)
insert into @p values (3,5)
insert into @p values (4,7)
insert into @p values (5,10)
insert into @p values (6,11)
insert into @p values (7,30)
insert into @p values (8,133)create table #result (result int)declare abc Cursor for select fld from @p order by id
open abc
Fetch next from abc into @pct
while @@Fetch_status=0
begin
SET @cnt=@cnt+@pct
set @idx=@idx+1
IF @idx=2
Begin
insert into #Result(Result) values (@cnt)
set @idx=0
set @cnt=0
end
fetch next from abc into @pct
end
select fld from @p
select * from #Result
drop table #Result
close abc
deallocate abc
select sum(字段2) from testtable
group by(字段1/10)
select ID=identity(int ,1,1),*
into #TempTable
from OldTable
order by field1select id/10,sum(field2)
from #TempTable
group by id/10