在线等，单表数据相加问题

[123456754321]

id3 deptid new_count
----------- ----------- -----------
1 1 15
2 1 25
3 1 70
11 1 40
1 2 60
3 3 80
10 3 50

如何得到：
id3 deptid new_count
----------- ----------- -----------
1 1 15
2 1 40
3 1 110
4 1 110
5 1 110
6 1 110
7 1 110
8 1 110
9 1 110
10 1 110
11 1 150
1 2 60
3 3 80
4 3 80
5 3 80
6 3 80
7 3 80
8 3 80
9 3 80
10 3 130

就是出现id大的只后就要求出以前id相加的值

[chinaandys]

写到这里，你试着再考虑一下
select id3,deptid
,net_count=(select sum(num_count) from tablename where id3<=a.id3)
from tablename a

[chinaandys]

id3 deptid new_count
----------- ----------- -----------
1 1 15
2 1 25
3 1 70
11 1 40
-------------------------------------------------上面的可以这样.
---------------------------------------------------------------------

你的表，建得似乎不符合数据的完整性、一致性

[vivianfdlpw]

--创建测试环境
create table tb(id3 int,deptid int,new_count int)
insert into tb
select 1,1,15 union all
select 2,1,25 union all
select 3,1,70 union all
select 11,1,40 union all
select 1,2,60 union all
select 3,3,80 union all
select 10,3,50
go

--查询
select identity(int,1,1) ID into # from syscolumns a ,syscolumns b

select B.ID
,A.deptid
,(select sum(new_count)
from tb
where deptid=A.deptid
and id3<=B.ID) as 'new_count'
from (select distinct deptid from tb)A
left join # B on B.ID
between
(select min(id3) from tb where deptid=A.deptid)
and
(select max(id3) from tb where deptid=A.deptid)
order by A.deptid
,B.ID

--删除测试环境
drop table #
drop table tb


--结果
/*
ID deptid new_count
----------- ----------- -----------
1 1 15
2 1 40
3 1 110
4 1 110
5 1 110
6 1 110
7 1 110
8 1 110
9 1 110
10 1 110
11 1 150
1 2 60
3 3 80
4 3 80
5 3 80
6 3 80
7 3 80
8 3 80
9 3 80
10 3 130

(20 row(s) affected)
*/