create table #aa (id int,cp varchar(10),qty int,totalqty int)
insert into #aa values(1,'a',100,130)
insert into #aa values(2,'a',20,130)
insert into #aa values(4,'b',20,130)
insert into #aa values(5,'a',20,130)

---要得到如下结果：

id      cp     qty   totalqty   ys
1    a    100    130     30
2    a    20    130     10
4    b    20    130     110
5    a    20    130     -10

select a.id,a.cp,a.qty,a.totalqty,max(a.totalqty)-sum(b.qty)
from #aa as a
    left join #aa as b
on a.cp=B.cp and a.id>=B.id
GROUP BY a.id,a.cp,a.qty,a.totalqty

------解决方案--------------------

SQL code

--> 测试数据：[tbl]
go
if object_id('[tbl]') is not null 
drop table [tbl]
go
create table [tbl](
[name] varchar(1),
[date] varchar(5),
[num] int
)
go
insert [tbl]
select 'a','1-1号',1 union all
select 'b','1-2号',4 union all
select 'a','1-3号',8 union all
select 'a','1-4号',5 union all
select 'b','1-5号',6 union all
select 'b','1-6号',9

;with t
as(
select ROW_NUMBER()over(partition by name
order by [date]) as id,
*,num  as total from tbl
),
m as(
select id,name,[date],num,total from t where id=1
union all
select a.id,a.name,a.[date],a.num,b.total+a.num from t a
inner join m b on a.id=b.id+1 and a.name=b.name
)
select name,[date],num,total from m order by name

/*
name    date    num    total
a    1-3号    8    8
a    1-4号    5    13
a    1-1号    1    14
b    1-2号    4    4
b    1-5号    6    10
b    1-6号    9    19
*/

------解决方案--------------------

SQL code

create table #aa (id int,cp varchar(10),qty int,totalqty int)
insert into #aa values(1,'a',100,130)
insert into #aa values(2,'a',20,130)
insert into #aa values(4,'b',20,130)
insert into #aa values(5,'a',20,130)

select a.id,a.cp,a.qty,a.totalqty,(max(a.totalqty)-sum(b.qty)) as ys
from #aa a left join #aa b on a.cp=b.cp and a.id>=B.id 
group by a.id,a.cp,a.qty,a.totalqty
order by cp

/*
id    cp    qty    totalqty    ys
1    a    100    130    30
2    a    20    130    10
5    a    20    130    -10
4    b    20    130    110
*/