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 getdate()) 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
*/

刚刚看错了你是要按照name来分别统计的，修改了一下

------解决方案--------------------
SQL code
 
declare @tb table  (
[name] varchar(1),
[date] varchar(5),
[num] int
)
 
insert into  @tb values('a','1-1号',1),('b','1-2号',4),('a','1-3号',8),( 'a','1-4号',8 ),('b','1-5号',6),('b','1-6号',9)
select name,num,date,total from (
 select *,
 (
 select SUM([num]) from ((select *,rn=ROW_NUMBER()over(partition by name order by name,date ) from @tb)
 )m 
 where s.name=m.name and s.date>=m.date) as total
from 
(select *,rn=ROW_NUMBER()over(partition by name order by name,date ) from @tb)s 
  )e order by name,date
 



name num         date  total
---- ----------- ----- -----------
a    1           1-1号  1
a    8           1-3号  9
a    8           1-4号  17
b    4           1-2号  4
b    6           1-5号  10
b    9           1-6号  19

(6 行受影响)

------解决方案--------------------
SQL code
 
declare @tb table  (
[name] varchar(1),
[date] varchar(5),
[num] int
)
 
insert into  @tb values('a','1-1号',1),('b','1-2号',4),('a','1-3号',8),( 'a','1-4号',8 ),('b','1-5号',6),('b','1-6号',9)
select name,num,date,total from (
 select *,
 (
 select SUM([num]) from ((select *  from @tb)
 )m 
 where s.name=m.name and s.date>=m.date) as total
from 
(select *  from @tb)s 
  )e order by name,date
 
name num         date  total
---- ----------- ----- -----------
a    1           1-1号  1
a    8           1-3号  9
a    8           1-4号  17
b    4           1-2号  4
b    6           1-5号  10
b    9           1-6号  19

(6 行受影响)