SQL如何实现累加
ID 商品名称 数量 累加值
1 牙刷 2
2 脸盆 3
3 鞋子 3
4 衣服 2
怎么给累加值里填上这一行以上的累加值，比如id为1的累加值是2，ID为2的累加值是5，ID为3的累加值是8，以此类推
[解决办法]
----------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-12-11 16:12:19
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
--Feb 10 2012 19:13:17
--Copyright (c) Microsoft Corporation
--Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([ID] int,[商品名称] varchar(4),[数量] int,[累加值] int)
insert [test]
select 1,'牙刷',2,null union all
select 2,'脸盆',3,null union all
select 3,'鞋子',3,null union all
select 4,'衣服',2,null
go
;with t
as(
select
a.id,
(select sum([数量]) from test b where b.id<=a.ID) as [累加值]
from
test a
)
update test
set [累加值]=t.[累加值] from t where test.ID=t.ID
select * from test
/*
ID商品名称数量累加值
-----------------------------------------------
1牙刷22
2脸盆35
3鞋子38
4衣服210
*/
select * ,(select sum(数量) from tb b where b.id<=a.id ) from tb a
----------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-12-11 16:12:19
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
--Feb 10 2012 19:13:17
--Copyright (c) Microsoft Corporation
--Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([ID] int,[商品名称] varchar(4),[数量] int,[累加值] int)
insert [test]
select 1,'牙刷',2,null union all
select 2,'脸盆',3,null union all
select 3,'鞋子',3,null union all
select 4,'衣服',2,null
go
;with t
as(
select
id,商品名称,数量,isnull(累加值,数量) as 累加值 from test where id=1
union all
select a.id,a.商品名称,a.数量,a.数量+b.累加值
from test a inner join t b on a.ID=b.ID+1
)
update test
set 累加值=t.累加值 from t where t.ID=test.ID
select * from test
/*
ID商品名称数量累加值
1牙刷22
2脸盆35
3鞋子38
4衣服210
*/
--递归实现
