ID Name Number
1 A 50
2 A 70
3 A 75
4 A 85现在我要一SQL语句,取出来的数据要是ID Name Number tNumber
1 A 50 50
2 A 70 120
3 A 75 195
4 A 85 280就是第tNumber的结果为它的Number加上它之前的所有Number的结果
1 A 50
2 A 70
3 A 75
4 A 85现在我要一SQL语句,取出来的数据要是ID Name Number tNumber
1 A 50 50
2 A 70 120
3 A 75 195
4 A 85 280就是第tNumber的结果为它的Number加上它之前的所有Number的结果
*,
tNumber=(select sum(Number) from tb where id<=t.id)
from
tb t
*,
select sum(Number) from tb where id<=t.id) as tNumber
from
tb t
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[Name] varchar(1),[Number] int)
insert [tb]
select 1,'A',50 union all
select 2,'A',70 union all
select 3,'A',75 union all
select 4,'A',85
---查询---
select
*,
tNumber=(select sum(Number) from tb where id<=t.id)
from
tb t---结果---
ID Name Number tNumber
----------- ---- ----------- -----------
1 A 50 50
2 A 70 120
3 A 75 195
4 A 85 280(所影响的行数为 4 行)
select
*,
(select sum(Number) from tb where id<=t.id) as tNumber
from
tb t
from tb k
from table1 a ,table1 b
where a.ID>==b.ID
group by a.ID,a.Name,a.Number
go
create table tb1([ID] int,[Name] nvarchar(50),[Number] int)
insert tb1 select 1,'A',50 union all
select 2,'A',70 union all
select 3,'A',75 union all
select 4,'A',85
select ID,Name,Number,
(select SUM(Number) from tb1 where ID<=a.ID )
tNumber from tb1 a
(select sum(Number) from tb where id<=t.id) as tNumber
from tb t