求一条SQL逐条计数语句

来源：百度知道 编辑：UC知道 时间：2024/05/28 10:52:15
例如SQL Server中有表TestValue（CREATE TABLE [dbo].[TestValue] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[BillNo] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[BillMan] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[ValueA] [int] NULL ,
[ValueB] [int] NULL
) ON [PRIMARY]）中提取数据，表原始数据如下：(其中ValueA/ValueB肯定有一个值为零，另一个非零）
Select * from TestValue Where BillNo = 'A'
ID BillNO BillMan ValueA ValueB
------------------------------------------
1 A A1 1 0
2 A A1 1 0
3 A A2 0 10
5 A A2 1 0
6 A A2 0 25
7 A A4 1 0
8 A A6 0 100

想要得到如下数据，SQL语句怎么写？
SNo BillMan ThisValue StartValue EndValue
--------------------------------------------------
1 A1 1 1 2
2 A1 1 2 3
3 A2 10 3 13
4 A2 1 13 14
5 A2 25 14 39
6 A4 1 39 40
7 A6 100 40 140
只有30分了，求不吝赐教，先行谢谢。

Select ID as SNo,BillMan,(ValueA+ValueB) as ThisValue,(EndValue-ThisValue) as StartValue,(select sum(ValueA+ValueB)+1 from TestValue where ID<=a.ID) as EndValue from TestValue a Where BillNo = 'A'

select t.id,t.billno,t.billman,t.this_value,
sum(t.this_value)over( order by id rows 100 preceding)-t.this_value+1 start_value,
sum(t.this_value)over( order by id rows 100 preceding)-t.this_value+1+t.this_value end_value
from
(Select id,billno,billman,valuea+valueb this_value
from TestValue
where billno='A') t

这是结果

ID BILLNO BILLMAN THIS_VALUE START_VALUE END_VALUE
---------- -------------------- -------------------- ---------- ----------- ----------
1 A A1 1 1 2
2 A A1 1 2 3
3 A A2