tabid name type qty sum
1 A a 2000
2 A b -1000
3 A c 3000
4 A a 2000
5 B b 1000
6 C d -1000
7 B d 1000
8 B b -1000
要求計算sum=相同(name)(type) 情況下 (qty)累加
如:
id name type qty sum
1 A a 2000 2000
2 A b -1000 -1000
3 A c 3000 3000
4 A a 2000 4000
5 B b 1000 1000
6 C d -1000 -1000
7 B d 1000 1000
8 B b -1000 0
1 A a 2000
2 A b -1000
3 A c 3000
4 A a 2000
5 B b 1000
6 C d -1000
7 B d 1000
8 B b -1000
要求計算sum=相同(name)(type) 情況下 (qty)累加
如:
id name type qty sum
1 A a 2000 2000
2 A b -1000 -1000
3 A c 3000 3000
4 A a 2000 4000
5 B b 1000 1000
6 C d -1000 -1000
7 B d 1000 1000
8 B b -1000 0
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id int,name varchar(1),type varchar(1),qty int,[sum] sql_variant)
insert into #tb
select 1,'A','a',2000,null union all
select 2,'A','b',-1000,null union all
select 3,'A','c',3000,null union all
select 4,'A','a',2000,null union all
select 5,'B','b',1000,null union all
select 6,'C','d',-1000,null union all
select 7,'B','d',1000,null union all
select 8,'B','b',-1000,nullupdate #tb
set [sum]=(select sum(qty) from #tb where name=t.name and type=t.type and id<=t.id)
from #tb t
select * from #tbid name type qty sum
----------- ---- ---- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 A a 2000 2000
2 A b -1000 -1000
3 A c 3000 3000
4 A a 2000 4000
5 B b 1000 1000
6 C d -1000 -1000
7 B d 1000 1000
8 B b -1000 0(8 行受影响)
insert into tb select 1,'A','a',2000,null
insert into tb select 2,'A','b',-1000,null
insert into tb select 3,'A','c',3000,null
insert into tb select 4,'A','a',2000,null
insert into tb select 5,'B','b',1000,null
insert into tb select 6,'C','d',-1000,null
insert into tb select 7,'B','d',1000,null
insert into tb select 8,'B','b',-1000,null
go
update tb set [sum]=(
case when not exists(select 1 from tb where name=a.name and type=a.type and id<a.id) then qty
else (select sum(qty) from tb where name=a.name and type=a.type and id<=a.id) end
)
from tb a
select * from tb
go
drop table tb
/*
id name type qty sum
----------- ---------- ---------- ----------- -----------
1 A a 2000 2000
2 A b -1000 -1000
3 A c 3000 3000
4 A a 2000 4000
5 B b 1000 1000
6 C d -1000 -1000
7 B d 1000 1000
8 B b -1000 0(8 行受影响)*/
(select name,type,sum(qty) as sum from a group by name,type) B where A.name =B.name and A.type =B.type order by A.id
select 1,'A','a',2000 union
select 2,'A','b',-1000 union
select 3,'A','c',3000 union
select 4,'A','a',2000 union
select 5,'B','b',1000 union
select 6,'C','d',-1000 union
select 7,'B','d',1000 union
select 8,'B','b',-1000
select A.id,A.name,A.type,A.qty,B.sum from #tab A,
(select name,type,sum(qty) as sum from #tab group by name,type) B where A.name =B.name and A.type =B.type order by A.id
create table #tab (id int,name nvarchar(2),type nvarchar(2),qty int,sum int)insert into #tab (id,name,type,qty)
select 1,'A','a',2000 union
select 2,'A','b',-1000 union
select 3,'A','c',3000 union
select 4,'A','a',2000 union
select 5,'B','b',1000 union
select 6,'C','d',-1000 union
select 7,'B','d',1000 union
select 8,'B','b',-1000select A.id,A.name,A.type,A.qty,(select sum(qty) from #tab where id <=A.id and name=A.name and type=A.type) as sum from #tab A