SQL上的信用借方余额计算 [英] Credit debit balance calculation on SQL

问题描述

我想在SQL查询中像这样平衡如何做到这一点.. ??

 + ------ + ------- +  - ------- + --------- + 
 | Id |借记|信用|平衡| 
 + ------ + ------- + -------- + --------- + 
 | 1 | 10 | 0 | 10 | 
 | 2 | 0 | 40 | -30 | 
 | 3 | 50 | 0 | 20 | 
 | 4 | 0 | 10 | 10 | 
 | 5 | 0 | 10 | 0 | 
 + ------ + ------- + -------- + --------- + 

我尝试过：

 SELECT x.Id 
，x.debit 
，x.credit 
，SUM（y.Balance）余额
 FROM 
（
 SELECT *，debit-credit bal FROM Ledger 
）x 
 JOIN 
（
 SELECT *，debit-credit bal FROM Ledger 
）y 
 ON y.Id< = x.Id 
 GROUP 
 BY x.Id，x.debit，x.credit; 

解决方案

同意PIEBALDConsult，但我将添加这个：



每个连续行的余额取决于前一行的期末余额。你可以编写SQL来循环遍历每个事务，或者按照建议，查看递归表达式。



尝试其中一个然后发布你尝试了什么。

我强烈建议你阅读：计算SQL Server中的简单运行总计 [ ^ ]。

您可以从这里使用相同的概念：如何计算运行总计中的空值？ [ ^ ]



Onl y不同的是，首先查询需要找到借方和贷方之间的差异，然后运行运行总计算。请参阅下面的示例。

  DECLARE   @ RunTotalTestData  表（
 Id  int   not not   null   identity （ 1  ， 1 ） primary   key ，
借记 int   null ，
 Credit  int   null  
）; 
 
  INSERT   INTO   @ RunTotalTestData （借方，贷方） VALUES （ 10 ， 0 ）; 
  INSERT   INTO   @ RunTotalTestData  （借方，贷方） VALUES （ 0 ， 40 ）; 
  INSERT   INTO   @ RunTotalTestData  （借方，贷方） VALUES （ 50 ， 0 ）; 
  INSERT   INTO   @ RunTotalTestData  （借方，贷方） VALUES （ 0 ， 10 ）; 
  INSERT   INTO   @ RunTotalTestData  （借方，贷方） VALUES （ 0 ， 10 ）; 
  INSERT   INTO   @ RunTotalTestData  （借方，贷方） VALUES （ 100 ， 10 ）; 
  INSERT   INTO   @ RunTotalTestData  （借方，贷方） VALUES （ 0 ， 10 ）; 
  INSERT   INTO   @ RunTotalTestData  （借方，贷方） VALUES （ 0 ， 110 ）; 
;  WITH  tempDebitCredit  AS （
  SELECT  a.id，a.debit，a.credit，a.Debit  -  a.Credit '  diff' 
  FROM   @ RunTotalTestData  a 
）
  SELECT  a.id，a.Debit，a.Credit，SUM（b.diff）' < span class =code-string> Balance' 
  FROM  tempDebitCredit a，
 tempDebitCredit b 
  WHERE  b.id< = a.id 
  GROUP   BY  a.id，a.Debit，a.Credit 

输出：

< pre lang =text> id借方贷方余额
1 10 0 10
2 0 40 -30
3 50 0 20
4 0 10 10
5 0 10 0
6 100 10 90
7 0 10 80
8 0 110 -30

I want Balance Like this in SQL Query How to do this..??

+------+-------+--------+---------+
|   Id | debit | credit | balance |
+------+-------+--------+---------+
|    1 |    10 |      0 |      10 |
|    2 |     0 |     40 |     -30 |
|    3 |    50 |      0 |      20 |
|    4 |     0 |     10 |      10 |
|    5 |     0 |     10 |       0 |
+------+-------+--------+---------+ 

What I have tried:

SELECT x.Id
     , x.debit
     , x.credit
     , SUM(y.Balance) balance 
  FROM
     ( 
       SELECT *,debit-credit bal FROM Ledger
     ) x
  JOIN
     ( 
       SELECT *,debit-credit bal FROM Ledger
     ) y
    ON y.Id<= x.Id
 GROUP 
    BY x.Id,x.debit,x.credit;

解决方案

Agree with PIEBALDConsult, but I'll add this:

The balance for each successive row depends on the ending balance from the previous row. You can either write SQL to loop through each transactions one at a time or, as has been suggested, look into recursive expressions.

Try one or the other and then post what you've tried.

I'd strongly recommend to read this: Calculating simple running totals in SQL Server[^].

You can use the same concept from here: How do I account for null values in a running total?[^]

Only different is, first the query need to find the different between the debit and credit, then run the running total calculation. See below as an example.

DECLARE @RunTotalTestData TABLE  (
   Id    int not null identity(1,1) primary key,
   Debit int null,
   Credit int null
);
 
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (10, 0);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (0, 40);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (50, 0);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (0, 10);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (0, 10);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (100, 10);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (0, 10);
INSERT INTO @RunTotalTestData (debit, Credit) VALUES (0, 110);
;WITH tempDebitCredit AS (
	SELECT a.id, a.debit, a.credit, a.Debit - a.Credit 'diff'
	FROM @RunTotalTestData a
)
SELECT a.id, a.Debit, a.Credit, SUM(b.diff) 'Balance'
FROM   tempDebitCredit a,
       tempDebitCredit b
WHERE b.id <= a.id
GROUP BY a.id,a.Debit, a.Credit

Output:

id	Debit	Credit	Balance
1	10	     0	     10
2	0	     40	    -30
3	50	     0	     20
4	0	     10	     10
5	0	     10	      0
6	100	     10	     90
7	0	     10	     80
8	0	     110	-30

这篇关于SQL上的信用借方余额计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！