请问在SQL 2000中如何对某字段分组累加计算啊.比如我有下面的数据:
Field1 Field2
li 20
li 30
li 10
lu 40
lu 20
我想得到下面的累加字段Field3
Field1 Field2 Field3
li 20 20
li 30 50
li 10 60
lu 40 40
lu 20 60
请各位大侠指教!
处理方法１：
select identity(int,1,1)as id,* into #temp from table
select field1,field2,(select sum(field2) from #temp where id<=a.id and field1=a.field1) from #temp a
处理方法２：
create table #t (Field1 varchar(10) , Field2 int)
insert into #t select 'li' , 20
insert into #t select 'li' , 30
insert into #t select 'li' , 10
insert into #t select 'lu' , 40
insert into #t select 'lu' , 20
select identity(int,1,1) as tid ,* into #temp from #t
select Field1 , Field2, (select sum(Field2) from #temp where Field1=A.field1 and tid<= A.tid ) as Field3
from (select * from #temp) as A
drop table #t,#temp
原文地址：
我在此基础上做了一点点改进：
create table #t2 (Field1 varchar(10), Field2 int,Field3 int)
insert into #t2 select 'li' ,20,50
insert into #t2 select 'li' ,30,10
insert into #t2 select 'li' ,10,70
insert into #t2 select 'lu' ,40,20
insert into #t2 select 'lu' ,20,30
select * from #t2
select * from #temp2
select identity(int,1,1) as tid ,* into #temp2 from #t2
select Field1,Field2,Field3, (select sum(Field3)-sum(Field2)
from #temp2 where tid<= A.tid ) as Field4
from (select * from #temp2) as A
drop table #t,#temp
Field1=A.field1 and
阅读(899) | 评论(0) | 转发(0) |
