如何在 SQL 中计算运行余额

Question

我正在使用嵌入式德比数据库，我想通过计算借方和贷方金额来添加运行余额列，所以请告诉我解决方案代码和图像也可以在下面找到

(如您在图像平衡总数中看到的不准确(

SELECT 
V_DATE,FLAG,V_NUM,V_NARATION,
sum(VDTL.DR_AMOUNT) AS DEBIT,
sum(VDTL.CR_AMOUNT) AS CRIDIT,
sum(dr_amount)-sum(cr_amount) as total
FROM VOUCHARDETAIL AS VDTL
INNER JOIN VOUCHARMASTER AS VMST
ON VDTL.DTL_NUM =VMST.MST_NUM
WHERE (V_DATE BETWEEN '12-03-2017' AND '14-03-2017') AND (AC_CODE=60030002) 
GROUP BY
V_DATE,FLAG,V_NUM,V_NARATION
ORDER BY
V_DATE

在此处输入图像描述

Answer 1

这可能是一个可能的解决方案。

SELECT V_DATE,
       FLAG,
       V_NUM,
       V_NARATION,
       SUM(VDTL.DR_AMOUNT) OVER(ORDER BY V_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS DEBIT,
       SUM(VDTL.CR_AMOUNT) OVER(ORDER BY V_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CREDIT
(SUM(VDTL.DR_AMOUNT) OVER(ORDER BY V_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)-SUM(VDTL.CR_AMOUNT) OVER(ORDER BY V_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) as TOTAL
FROM VOUCHARDETAIL AS VDTL
INNER JOIN VOUCHARMASTER AS VMST
ON VDTL.DTL_NUM =VMST.MST_NUM
WHERE (V_DATE BETWEEN '12-03-2017' AND '14-03-2017') AND (AC_CODE=60030002) 
ORDER BY V_DATE

Answer 2

试试这个(

 select sel.*, sel.total + LAG(sel.total,1,0) over (order by Sel.V_DATE) as running_balance from (SELECT 
    V_DATE,FLAG,V_NUM,V_NARATION,
    sum(VDTL.DR_AMOUNT) AS DEBIT,
    sum(VDTL.CR_AMOUNT) AS CRIDIT,
    sum(dr_amount)-sum(cr_amount) as total,
    FROM VOUCHARDETAIL AS VDTL
    INNER JOIN VOUCHARMASTER AS VMST
    ON VDTL.DTL_NUM =VMST.MST_NUM
    WHERE (V_DATE BETWEEN '12-03-2017' AND '14-03-2017') AND (AC_CODE=60030002) 
    GROUP BY
    V_DATE,FLAG,V_NUM,V_NARATION
    ORDER BY
    V_DATE) Sel
    order by Sel.V_DATE