转自：

摘要:

今天接到一个需求,有一张数据表，记录的是消费明细数据，

现在需要做一个累计结余，记录每次的数据结余合计,

下文将展示一种sql脚本的编写方式

实验环境:sqlserver 2008 R2

如下例所示:

detail表 记帐流水表==========字段：qt_srMoney   (money) 收入qt_zcMoney  (money) 支出qt_date         (datetime) 操作日期qt_dkfs         (nvarchar)  打款方式------------------需获取一个带结余的数据信息==========根据期初表中的日期和金额，查询出记帐表中的结余。帐面结余 ＝期初金额+收入－支出实验环境:sql server 2008 R2*/ create table detail(qt_srMoney   money,qt_zcMoney  money,qt_date         datetime,qt_dkfs         nvarchar(100))insert into detail values(100,0,'2018-1-2 10:00:00','other')insert into detail values(0,10,'2018-2-2 10:00:00','other')insert into detail values(0,20,'2018-3-2 10:00:00','other')insert into detail values(30,0,'2018-4-2 10:00:00','other')insert into detail values(10,60,'2018-5-2 10:00:00','other')godeclare @datestart datetime ---开始计算日期set @datestart ='2018-2-1'declare @qcomney money --期初set @qcomney =100  select t1.qt_date,t1.qt_dkfs,t1.qt_srMoney,t1.qt_zcMoney,sum(t2.qt_srMoney-t2.qt_zcMoney)+@qcomneyfrom detail t1,detail t2 where t1.qt_date>=t2.qt_date and t1.qt_date >=@datestartgroup by t1.qt_date,t1.qt_dkfs,t1.qt_srMoney,t1.qt_zcMoneyorder by t1.qt_date;go---避免日期出现两条导致结余数据计算异常-----declare @datestart datetime ---开始计算日期set @datestart ='2018-2-1'declare @qcomney money --期初set @qcomney =100with cteName  as (  select qt_srMoney,qt_zcMoney,qt_date,qt_dkfs,    row_number() over ( order by qt_date)  as keyID  from detail where qt_date >=@datestart)  select t1.keyId,t1.qt_date,t1.qt_dkfs,t1.qt_srMoney,t1.qt_zcMoney,sum(t2.qt_srMoney-t2.qt_zcMoney)+@qcomneyfrom cteName t1,cteName t2 where t1.keyId>=t2.keyId  group by t1.qt_date,t1.qt_dkfs,t1.qt_srMoney,t1.qt_zcMoney,t1.keyIdorder by t1.keyId; gotruncate table detaildrop table detail