不是吧,查2006年1月3日~2006年1月9日,竟然还有2005年的记录???若不考虑速度问题,则可以试试下面的代码,否则建议建立一个临时表或者月份结余存档select
日期,
摘要,
借方,
贷方,
case when 余额<0 then '贷'
when 余额>0 then '借'
else '平'
end as 方向,
abs(余额) as 余额
from
(select --期初
0 as id,
'' as 日期,
'期初余额:' as 摘要,
'' as 借方,
'' as 贷方,
sum([贷方]*(-1)+[借方]) as 余额
from table1
where convert(varchar(10),日期,120)<'2006-01-01'
union all
select 1 as id,
日期,
摘要,
case 借方 when 0 then ''
else str(借方)
end as 借方,
case 贷方 when 0 then ''
else str(贷方)
end as 贷方,
(select sum(借方+贷方*(-1))
from table1
where 日期<a.日期
) as 余额
from table1 a
) b
日期,
摘要,
借方,
贷方,
case when 余额<0 then '贷'
when 余额>0 then '借'
else '平'
end as 方向,
abs(余额) as 余额
from
(select --期初
0 as id,
'' as 日期,
'期初余额:' as 摘要,
'' as 借方,
'' as 贷方,
sum([贷方]*(-1)+[借方]) as 余额
from table1
where convert(varchar(10),日期,120)<'2006-01-01'
union all
select 1 as id,
日期,
摘要,
case 借方 when 0 then ''
else str(借方)
end as 借方,
case 贷方 when 0 then ''
else str(贷方)
end as 贷方,
(select sum(借方+贷方*(-1))
from table1
where 日期<a.日期
) as 余额
from table1 a
) b
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货