select edate 日期, iin 收入, out 支出, (select sum(iin)-sum(out) from #table where edate <=A.edate) as 余額 from #table as A
2. select 日期,收入,支出,(select sum(收入-支出) from 表 where 日期<=aa.日期) 余額 from 表 aa
我想了一句,不知道行不行啊 思路:当天的余额=前一天的余额+当天的收入-当天的支出 select rq,sr,zc,(select t1.ye+t2.sr-t2.zc from table t1,table t2 where t1.rq = select top 1 rq from t2 where t1.rq<t2.rq order by rq desc) from table
create table #TABLE( edate datetime, iin int, out int)insert into #table select '2000/3/1', 50, 30 union all select '2000/3/2', 45,60 union all select '2000/3/5', 60,10 declare @bdate datetime declare @edate datetime select @bdate = min(edate) from #table select @edate = max(edate) from #table while @bdate < @edate begin set @bdate = dateadd(day, 1, @bdate) if not exists (select * from #table where edate = @bdate) insert into #table select @bdate, 0, 0 end select edate 日期, iin 收入, out 支出, (select sum(iin)-sum(out) from #table where edate <=A.edate) as 余額 from #table as A order by edate
(select sum(iin)-sum(out) from #table where edate <=A.edate) as 余額
from #table as A
select 日期,收入,支出,(select sum(收入-支出) from 表 where 日期<=aa.日期) 余額 from 表 aa
思路:当天的余额=前一天的余额+当天的收入-当天的支出
select rq,sr,zc,(select t1.ye+t2.sr-t2.zc from table t1,table t2 where t1.rq = select top 1 rq from t2 where t1.rq<t2.rq order by rq desc) from table
edate datetime,
iin int,
out int)insert into #table
select '2000/3/1', 50, 30
union all select '2000/3/2', 45,60
union all select '2000/3/5', 60,10
declare @bdate datetime
declare @edate datetime
select @bdate = min(edate) from #table
select @edate = max(edate) from #table
while @bdate < @edate
begin
set @bdate = dateadd(day, 1, @bdate)
if not exists (select * from #table where edate = @bdate)
insert into #table select @bdate, 0, 0
end
select edate 日期, iin 收入, out 支出,
(select sum(iin)-sum(out) from #table where edate <=A.edate) as 余額
from #table as A
order by edate
--------------------------- ----------- ----------- -----------
2000-03-01 00:00:00.000 50 30 20
2000-03-02 00:00:00.000 45 60 5
2000-03-03 00:00:00.000 0 0 5
2000-03-04 00:00:00.000 0 0 5
2000-03-05 00:00:00.000 60 10 55(5 row(s) affected)
from test l
join test r
on l.日期 >= r.日期
group by l.日期 ,l.收入,l.支出
你们都不让我来的说!haha..