明细表数据计算结余数

参考：https://www.cnblogs.com/lairui1232000/p/8964949.html

/*
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')
INSERT INTO detail VALUES (32., 0., '2018-04-03', 'other');
INSERT INTO detail VALUES (6., 0., '2018-04-03', 'other');
INSERT INTO detail VALUES (0., 8., '2018-04-03', 'other');

go 
 
 
 select * from detail




-- 方法一
SELECT
    t1.keyId,
    t1.qt_date,
    t1.qt_dkfs,
    t1.qt_srMoney,
    t1.qt_zcMoney,
    SUM ( t2.qt_srMoney- t2.qt_zcMoney ) AS amt 
FROM
    ( SELECT qt_srMoney, qt_zcMoney, qt_date, qt_dkfs, row_number ( ) OVER ( ORDER BY qt_date ) AS keyID FROM detail ) t1,
    ( SELECT qt_srMoney, qt_zcMoney, qt_date, qt_dkfs, row_number ( ) OVER ( ORDER BY qt_date ) AS keyID FROM detail ) t2 
WHERE
    t1.keyId>= t2.keyId 
GROUP BY
    t1.qt_date,
    t1.qt_dkfs,
    t1.qt_srMoney,
    t1.qt_zcMoney,
    t1.keyId 
ORDER BY
    t1.keyId;


-- 方法二
SELECT
    *,
    (
    SELECT
        isnull( SUM ( ta.qt_srMoney - ta.qt_zcMoney ), 0 ) 
    FROM
        detail AS ta 
    WHERE
        ( ta.qt_date< T.qt_date OR ( ta.qt_date= T.qt_date AND ta.id< T.id ) ) 
    ) + T.qt_srMoney - T.qt_zcMoney AS Total 
FROM
    detail AS T 
ORDER BY
    qt_date ASC