--> 测试数据：[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
*/
-------------------------------------------------------


--> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([ID] int,[借方] int,[贷方] int)
insert [tbl]
select 1,10,0 union all
select 2,0,4  union all
select 3,0,2  union all
select 4,1,0

SELECT 
ID,借方,贷方,
[余额]=(SELECT SUM(借方-贷方) FROM tbl WHERE ID<=a.ID)
FROM tbl AS a

分别用日期，年份和月份，年份分组排序，然后找上面给的资料区更改递归

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

SQL code

create table t1
(
    id int,
    riqi datetime,
    rileiji int,
    yueleiji int,
    nianleiji int
)
insert into t1
select 1, '2010-6-9', 490472, 0, 0 union all
select 1, '2010-6-10', 475110, 0, 0 union all
select 1, '2010-7-2', 455993, 0, 0 union all
select 1, '2010-7-30', 791037, 0, 0 union all
select 1, '2010-7-31', 802321, 0, 0 union all
select 1, '2010-8-21', 517457, 0, 0 union all
select 1, '2010-8-22', 531709, 0, 0 union all
select 1, '2010-9-1', 508646, 0, 0 union all
select 1, '2010-9-2', 521942, 0, 0 union all
select 1, '2010-9-30', 514238, 0, 0 union all
select 1, '2010-10-1', 487104, 0, 0 union all
select 1, '2010-10-31', 626030, 0, 0 union all
select 1, '2010-11-1', 524300, 0, 0 union all
select 1, '2010-11-23', 1356601, 0, 0 union all
select 1, '2010-12-5', 1477584, 0, 0 union all
select 1, '2010-12-25', 1716936, 0, 0 union all
select 1, '2010-12-31', 1685794, 0, 0 union all
select 1, '2011-1-1', 1720004, 0, 0 union all
select 1, '2011-1-2', 1662292, 0, 0 union all
select 1, '2011-2-14', 1664969, 0, 0 union all
select 1, '2011-2-15', 1639400, 0, 0
select * from t1 

select a.id,a.riqi,a.rileiji,
case when DAY(a.riqi)=1 
    then (select SUM(rileiji) from t1 where riqi>=convert(varchar(7),DATEADD(MONTH,-1,a.riqi),120)+'-02'
            and riqi<=a.riqi)
    else (select SUM(rileiji) from t1 where riqi>=convert(varchar(7),a.riqi,120)+'-02'
            and riqi<=a.riqi)
end as yueleiji,
case when MONTH(a.riqi)=1 and DAY(a.riqi)=1
    then (select SUM(rileiji) from t1 where riqi>=convert(varchar(7),DATEADD(YEAR,-1,a.riqi),120)+'-01'
            and riqi<=a.riqi)
    else (select SUM(rileiji) from t1 where riqi&