create table #t
(品项 varchar(10),数量 int)insert into #t
select 'A',30 union all
select 'B',20
with t as
(select *,row_number() over(order by 品项) 'rn'
from #t
)
select a.品项,
isnull((select sum(b.数量)+1 from t b where b.rn<a.rn),1) '开始数',
(select sum(b.数量) from t b where b.rn<=a.rn) '结束数'
from t a/*
品项 开始数 结束数
---------- ----------- -----------
A 1 30
B 31 50(2 row(s) affected)
*/
,b as(select ROW_NUMBER()over(order by getdate()) as id,* from a)select 品项,isnull((select SUM(数量)+1 as 开始数 from b where b.id<c.id),1) as 开始数,(select SUM(数量) as 开始数 from b where b.id<=c.id) as 结束数 from b as c
/**
品项 开始数 结束数
------------------
a 1 30
b 31 50
------------------
**/