表 Tbs 字段 ID,cont
ID cont
1 0
2 3
3 0
4 1
5 0
6 1
写个sql 变成
ID cont sumcont =前面cont纪录的和
1 0 0
2 3 3
3 0 3
4 1 4
5 0 4
6 1 5
ID cont
1 0
2 3
3 0
4 1
5 0
6 1
写个sql 变成
ID cont sumcont =前面cont纪录的和
1 0 0
2 3 3
3 0 3
4 1 4
5 0 4
6 1 5
from tb a
insert into @tb select 1,0
insert into @tb select 2,3
insert into @tb select 3,0insert into @tb select 4,1
insert into @tb select 5,0
insert into @tb select 6,1select id,sumcout=(select sum(cont) from @tb where id<=tp.id)
from @tb tp
id sumcout
1 0
2 3
3 3
4 4
5 4
6 5
declare @tb table (id int,cont int)
insert into @tb select 1,0
insert into @tb select 2,3
insert into @tb select 3,0insert into @tb select 4,1
insert into @tb select 5,0
insert into @tb select 6,1select id,sumcout=(select isnull(sum(cont),0) from @tb where id<=tp.id)
from @tb tp
cont,
sumcout=(select sum(cont) from @ta where id<=a.id)
from ta a
declare @tb table (id int,cont int)
insert into @tb select 1,0
insert into @tb select 2,3
insert into @tb select 3,0insert into @tb select 4,1
insert into @tb select 5,0
insert into @tb select 6,1select id,cont,sumcout=(select sum(cont) from @tb where id<=tp.id)
from @tb tpid cont sumcout
----------- ----------- -----------
1 0 0
2 3 3
3 0 3
4 1 4
5 0 4
6 1 5(6 行受影响)
cont,
sumcout=(select sum(cont) from @ta where id<=a.id)
from ta a
Create table #t(Id int identity,cont int)
insert into #t select 0
insert into #t select 3
insert into #t select 0
insert into #t select 1
insert into #t select 0
insert into #t select 1select *,isnull((select sum(cont) from #t where a.id-1>=id),0)+cont from #t a/*
Id cont
----------- ----------- -----------
1 0 0
2 3 3
3 0 3
4 1 4
5 0 4
6 1 5(6 行受影响)
*/
select *,sumcont=(select sum(cont) from Tbs where id <=a.id) from Tbs a
主要我的ID 不一定 有重复 ID=1 有可能有2条以上纪录 不能判断唯一
--------------------------------------------------------
--那就select px=identity(int,1,1),* into # from tbsselect id,cont,sumcont=(select sum(cont) from # where px <=a.px) from # a