MySql 查询避免负余额 [英] MySql Query to Avoiding Negative Balance

问题描述

是否可以使用 MySql 查询来避免负余额?我有以下 MySql 表:

<前>trx_no trx_date 期初借记贷方1 2019-10-01 200 0 1002 2019-10-02 200 0 1003 2019-10-03 200 100 04 2019-10-03 200 400 05 2019-10-03 200 0 2006 2019-10-04 200 0 1007 2019-10-05 200 0 400

使用此查询:

SELECTtrx_no,trx_date，开幕，借方，信用，开仓 + (SELECT SUM(t2.credit - t2.debit)从 MyTable t2WHERE t2.trx_no <= t1.trx_no) AS 余额从 MyTable t1订购者trx_no;

我得到了:

trx_no trx_date 期初借方贷方余额1 2019-10-01 200 0 100 3002 2019-10-02 200 0 100 4003 2019-10-03 200 100 0 3004 2019-10-03 200 400 0 -1005 2019-10-03 200 0 200 1006 2019-10-04 200 0 100 2007 2019-10-05 200 0 400 600

您可以看到 2019-10-03 有负余额 (-100).是否可以通过首先允许信用计算 如果在同一日期有借方和贷方来进行mysql查询以避免负余额?所以结果会变成:

trx_no trx_date 期初借方贷方余额1 2019-10-01 200 0 100 3002 2019-10-02 200 0 100 4005 2019-10-03 200 0 200 6003 2019-10-03 200 100 0 5004 2019-10-03 200 400 0 1006 2019-10-04 200 0 100 2007 2019-10-05 200 0 400 600

数据库小提琴

解决方案

条件:

WHERE t2.trx_no <= t1.trx_no

不适用于您的情况.
您需要按 trx_date 排序的行，然后是贷方，然后是借方，最后是 trx_no.
此代码将使用这 3 个条件的组合列(连接):

SELECTt1.trx_no,t1.trx_date，t1.Opening,t1.借方，t1.credit,t1.Opening + (选择总和(t2.credit - t2.debit)从 MyTable t2在哪里concat(t2.trx_date, t2.debit > t2.credit, lpad(t2.trx_no, 10, '0')) <=concat(t1.trx_date, t1.debit > t1.credit, lpad(t1.trx_no, 10, '0'))) AS 余额从 MyTable t1ORDER BY concat(t1.trx_date, t1.debit > t1.credit, lpad(t1.trx_no, 10, '0'))

查看演示.
结果:

<代码>|trx_no |trx_date |开幕 |借记|信用|平衡||------ |---------- |------- |----- |------ |------- ||1 |2019-10-01 |200 |0 |100 |300 ||2 |2019-10-02 |200 |0 |100 |400 ||5 |2019-10-03 |200 |0 |200 |600 ||3 |2019-10-03 |200 |100 |0 |500 ||4 |2019-10-03 |200 |400 |0 |100 ||6 |2019-10-04 |200 |0 |100 |200 ||7 |2019-10-05 |200 |0 |400 |600 |

Is that possible to avoid negative balance using MySql query? I have the following MySql table:

trx_no  trx_date    Opening debit   credit 
1       2019-10-01  200     0       100    
2       2019-10-02  200     0       100     
3       2019-10-03  200     100     0       
4       2019-10-03  200     400     0      
5       2019-10-03  200     0       200      
6       2019-10-04  200     0       100      
7       2019-10-05  200     0       400      

with this query:

SELECT
    trx_no,
    trx_date,
    Opening,
    debit,
    credit,
    Opening + (SELECT SUM(t2.credit - t2.debit)
               FROM MyTable t2
               WHERE t2.trx_no <= t1.trx_no) AS balance
FROM MyTable t1
ORDER BY
    trx_no;

I got:

trx_no  trx_date    Opening debit   credit   balance
1       2019-10-01  200     0       100      300
2       2019-10-02  200     0       100      400
3       2019-10-03  200     100     0        300
4       2019-10-03  200     400     0       -100
5       2019-10-03  200     0       200      100
6       2019-10-04  200     0       100      200
7       2019-10-05  200     0       400      600

You can see that there is a negative balance (-100) on 2019-10-03. Is that possible to make a mysql query to avoid negative balance by allowing credit calculation first if there are debit and credit on the same date? so the result will become:

trx_no  trx_date    Opening debit   credit   balance
    1   2019-10-01  200     0       100      300
    2   2019-10-02  200     0       100      400
    5   2019-10-03  200     0       200      600
    3   2019-10-03  200     100     0        500
    4   2019-10-03  200     400     0        100
    6   2019-10-04  200     0       100      200
    7   2019-10-05  200     0       400      600

DB Fiddle

解决方案

The condition:

WHERE t2.trx_no <= t1.trx_no

does not work in your case.
You need the rows ordered by trx_date, then by credits first and then debits and finally by trx_no.
This code will use a combined column (with concatenation) of these 3 conditions:

SELECT
    t1.trx_no,
    t1.trx_date,
    t1.Opening,
    t1.debit,
    t1.credit,
    t1.Opening + (
      SELECT SUM(t2.credit - t2.debit)
      FROM MyTable t2 
      WHERE 
        concat(t2.trx_date, t2.debit > t2.credit, lpad(t2.trx_no, 10, '0')) <=
        concat(t1.trx_date, t1.debit > t1.credit, lpad(t1.trx_no, 10, '0'))
    ) AS balance
FROM MyTable t1
ORDER BY concat(t1.trx_date, t1.debit > t1.credit, lpad(t1.trx_no, 10, '0'))

See the demo.
Results:

| trx_no | trx_date   | Opening | debit | credit | balance |
| ------ | ---------- | ------- | ----- | ------ | ------- |
| 1      | 2019-10-01 | 200     | 0     | 100    | 300     |
| 2      | 2019-10-02 | 200     | 0     | 100    | 400     |
| 5      | 2019-10-03 | 200     | 0     | 200    | 600     |
| 3      | 2019-10-03 | 200     | 100   | 0      | 500     |
| 4      | 2019-10-03 | 200     | 400   | 0      | 100     |
| 6      | 2019-10-04 | 200     | 0     | 100    | 200     |
| 7      | 2019-10-05 | 200     | 0     | 400    | 600     |

这篇关于MySql 查询避免负余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！