如何在SQL中计算运行余额 [英] How to calculate running balance in SQL

问题描述

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



（正如您在图像平衡中看到的总计不准确）

  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 总计
  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 （ FLAG = '  IV' 或 FLAG = '  BR' 
 或 FLAG = '  CP' 或 FLAG = '  CR' 或 FLAG = '  JV'  OR  FLAG = '  BP'） AND （AC_CODE = 60030002）
  GROUP   BY  
 V_DATE，FLAG，V_NUM，V_NARATION 
  ORDER   BY  
 V_DATE 

我尝试过：



运行余额列需要

解决方案

要获得运行余额，可以使用Sum（value）Over（Order by）子句，如

 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）为总计，
  sum（dr_amount-cr_amount）结束（按V_DATE排序） 
来自VOUCHARDETAIL作为VDTL 
 INNER JOIN VOUCHARMASTER作为VMST 
 ON VDTL.DTL_NUM = VMST.MST_NUM 
 WHERE（V_DATE BETWEEN '12 -03-2017'和'14 -03-2017' ）AND（FLAG ='IV'或FLAG ='BR'
或FLAG ='CP'或FLAG ='CR'或FLAG ='JV'或FLAG ='BP'）和（AC_CODE = 60030002）
 GROUP BY 
 V_DATE，FLAG，V_NUM，V_NARATION 
 ORDER BY 
 V_DATE 

I am using embedded derby database and I want to add running balance column by calculating debit and credit amounts so please tell me solution code and image are also available below

(as you can see in image balance total is not accurate)

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 (FLAG='IV' OR FLAG='BR'
OR FLAG='CP' OR FLAG='CR' OR FLAG='JV' OR FLAG='BP') AND (AC_CODE=60030002) 
GROUP BY
V_DATE,FLAG,V_NUM,V_NARATION
ORDER BY
V_DATE

What I have tried:

running balance column require

解决方案

To get running balance you can use Sum(value)Over(Order by ) clause like

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,
sum(dr_amount-cr_amount)Over(Order by V_DATE)
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 (FLAG='IV' OR FLAG='BR'
OR FLAG='CP' OR FLAG='CR' OR FLAG='JV' OR FLAG='BP') AND (AC_CODE=60030002) 
GROUP BY
V_DATE,FLAG,V_NUM,V_NARATION
ORDER BY
V_DATE

这篇关于如何在SQL中计算运行余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！