表结构: 借方金额 贷方金额 日期
100 20 2006-1-1
80 30 2006-1-1
200 40 2006-1-2
300 50 2006-1-2报表格式:(期初余额=100) 借方金额 贷方金额 余额 日期
100 20 180 2006-1-1
80 30 230 2006-1-1
200 40 390 2006-1-2
300 50 640 2006-1-2
其中: 第一笔余额列的值=(借方金额 - 贷方金额)+期初余额 =180
第二笔余额列的值=(借方金额 - 贷方金额)+上一条余额 =230
依此类推请问这样的查询语句该怎么样实现?????谢谢大家帮助!!!!!
100 20 2006-1-1
80 30 2006-1-1
200 40 2006-1-2
300 50 2006-1-2报表格式:(期初余额=100) 借方金额 贷方金额 余额 日期
100 20 180 2006-1-1
80 30 230 2006-1-1
200 40 390 2006-1-2
300 50 640 2006-1-2
其中: 第一笔余额列的值=(借方金额 - 贷方金额)+期初余额 =180
第二笔余额列的值=(借方金额 - 贷方金额)+上一条余额 =230
依此类推请问这样的查询语句该怎么样实现?????谢谢大家帮助!!!!!
insert into @t select 100,20,'2006-1-1'
insert into @t select 80 ,30,'2006-1-1'
insert into @t select 200,40,'2006-1-2'
insert into @t select 300,50,'2006-1-2'select 借方金额,贷方金额,余额=0,日期 into # from @tdeclare @sum int
set @sum=100
update #
set
@sum=@sum+借方金额-贷方金额,
余额=@sumselect * from #
/*
借方金额 贷方金额 余额 日期
----------- ----------- ----------- ----------
100 20 180 2006-1-1
80 30 230 2006-1-1
200 40 390 2006-1-2
300 50 640 2006-1-2
*/drop table #
declare @chu int
select @chu=100
select 借方金额,
贷方金额,
余额=case when (select 1 from 表 where id<a.id) is null then @chu+借方金额-贷方金额
else (select sum(余额) from 表 where id<a.id)+借方金额-贷方金额 end,
日期
from 表 a
insert into @t select 1, 100,20,'2006-1-1'
insert into @t select 2,80 ,30,'2006-1-1'
insert into @t select 3,200,40,'2006-1-2'
insert into @t select 4,300,50,'2006-1-2'
--try
declare @chu int
select @chu=100select 借方金额,
贷方金额,
余额=case when (select count(*) from @t where id<a.id)=0 then @chu+借方金额-贷方金额
else (select sum(借方金额-贷方金额) from @t where id<a.id)+借方金额-贷方金额 end,
日期
from @t a
/*
100 20 180 2006-1-1
80 30 130 2006-1-1
200 40 290 2006-1-2
300 50 540 2006-1-2*/
200 40 390 2006-1-2
300 50 640 2006-1-2但是现在做不到。请问如何改写???
select @chu=100select 借方金额,
贷方金额,
余额=case when (select count(*) from @t where id<a.id)=0 then @chu+借方金额-贷方金额
else (select @chu+sum(借方金额-贷方金额) from @t where id<a.id)+借方金额-贷方金额 end,
日期
from @t a
/*
100 20 180 2006-1-1
80 30 230 2006-1-1
200 40 390 2006-1-2
300 50 640 2006-1-2
*/
select @chu=100select 借方金额,
贷方金额,
余额=case when (select count(*) from @t where id<a.id and 日期='2006-1-2')=0 then @chu+借方金额-贷方金额
else (select @chu+sum(借方金额-贷方金额) from @t where id<a.id and 日期='2006-1-2')+借方金额-贷方金额 end,
日期
from @t a
where 日期='2006-1-2'
--要加筛选条件,就在相关的地方加。
insert into @t select 1, 100,20,'2006-1-1'
insert into @t select 2,80 ,30,'2006-1-1'
insert into @t select 3,200,40,'2006-1-2'
insert into @t select 4,300,50,'2006-1-2'
--try
declare @chu int
select @chu=100select 借方金额,
贷方金额,
余额=case when (select count(*) from @t where id<a.id )=0 then @chu+借方金额-贷方金额
else (select @chu+sum(借方金额-贷方金额) from @t where id<a.id )+借方金额-贷方金额 end,
日期
from @t a
where 日期='2006-1-2'--此处是要加筛选条件的地方
/*
200 40 390 2006-1-2
300 50 640 2006-1-2
*/
insert into @t
select 1,100, 20, '2006-1-1' union
select 2,80 ,30 , '2006-1-1' union
select 3,200, 40, '2006-1-2' union
select 4,300, 50, '2006-1-2'
select id ,
c_money as 借方金额,
d_money as 贷方金额,
(c_money - d_money + (select isnull(sum(c_money),0) from @t b where b.id < a.id) - (select isnull(sum(d_money),0) from @t b where b.id < a.id) + 100) as 余额,
date
from @t a