用于运行余额校正的 MySql 查询

Question

请有人帮我做一个 MySql 查询来进行运行余额校正。

实际上我们有很多来自其他应用程序的脏数据。有时我们在正常的运行余额计算中会得到负余额。

来自the question before我得到了答案，但我希望仅在正常余额计算为负数时才首先计算贷方交易，因此如果余额不为负数，则同一日期内借方和贷方的顺序不应更改

这是表格示例:

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     100     0 
8       2019-10-05  200     0       400

对于正常余额计算，我们得到负余额:

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  <- negative balance
    5   2019-10-03  200     0       200      100
    6   2019-10-04  200     0       100      200
    7   2019-10-05  200     100     0        100
    8   2019-10-05  200     0       400      500

这是我从 @forpas 收到的查询(非常感谢):

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'))

下面是上述查询的结果，没有负余额，但 trx_no 8 在 trx_no 7 之前订购:

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
    8   2019-10-05  200     0       400      600
    7   2019-10-05  200     100     0        500

我们需要的结果如下。信用交易仅在余额为负数时才计算。因此，如果余额不为负，则不应更改订单:

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     100     0        100
    8   2019-10-05  200     0       400      500

这是DEMO

Answer 1

我认为这不能使用基于集合的代码来完成，因为您需要预读和回读。一些程序性的东西可能会起作用。使用游标和临时表我几乎可以完成此任务(您需要添加一些内容以确保累积余额不会变为负数)。根据你的说法，第一笔交易永远不会使余额为负数，但我会检查这一点。请注意，贷方池中的支出可能来自比支出补偿的借方晚的日期 - 因为余额永远不会变为负数，所以这不应该成为问题(原文如此)。当然，我并不期望性能会很好。

DROP TABLE IF EXISTS T;
create table t
(trx_no int, trx_date date,    Opening int,debit int,  credit int);
insert into t values
(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  ,   100   ,  0  ),
(8    ,   '2019-10-05' , 200  ,   0     ,  400);

DROP TABLE IF EXISTS stmT;
create table stmt
(trx_no int, trx_date date,    Opening int,debit int,  credit int, cumbal int);




drop procedure if exists p;
delimiter $$
create procedure p()
begin

 declare v_trx_no int;
 declare v_dt date;
 declare v_debit int;
 declare v_credit int;

 declare h_trx_no int;
 declare h_dt date;
 declare h_credit int;

 declare opbal int;
 declare rbal  int;
 declare done int default 0;
 declare cur cursor for select trx_no,trx_date,debit,credit from t order by trx_date,trx_no;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

 select opening into opbal from t order by trx_no limit 1;
 set rbal = opbal;
 drop table if exists creditpool;
 create temporary table if not exists creditpool as select trx_no,trx_date,credit,0 as used from t where  credit > 0; 
 #select * from creditpool;
    open cur;
    cursorloop:loop
        fetch cur into v_trx_no,v_dt,v_debit,v_credit;
         if done = true then  
        leave cursorloop;
         end if;  
         if v_credit > 0 and exists (select 1 from creditpool where trx_no = v_trx_no and used = 0) then 
             if v_credit > 0 then
                 insert into stmt values (v_trx_no,v_dt,opbal,v_debit,v_credit, rbal + v_credit);
                 set rbal = rbal + v_credit;
                 update creditpool set used = 1 where trx_no = v_trx_no; 
            end if;
         end if;
         if v_debit > 0 and v_debit <= rbal then
             insert into stmt values (v_trx_no,v_dt,opbal,v_debit,v_credit, rbal - v_debit);
             set rbal = rbal - v_debit;
         elseif v_debit > 0 and v_debit > rbal then
             poolloop: loop
                        select trx_no,trx_date,credit
                            into h_trx_no,h_dt,h_credit
                            from creditpool
                            where used = 0 
                            order by trx_date,trx_no limit 1;
                        insert into stmt values (h_trx_no,h_dt,opbal,0,h_credit, rbal + h_credit);
                        set rbal = rbal + h_credit;
                        update creditpool set used = 1 where trx_no = h_trx_no; 

                        if v_debit <= rbal then
                            insert into stmt values (v_trx_no,v_dt,opbal,v_debit,v_credit, rbal - v_debit);
                            set rbal = rbal - v_debit;
                            leave poolloop;
                        end if;
             end loop poolloop;           
         end if;
    end loop cursorloop;
    close cur;
end $$
delimiter ;
truncate stmt;
call p();
select * from stmt;

+--------+------------+---------+-------+--------+--------+
| trx_no | trx_date   | Opening | debit | credit | cumbal |
+--------+------------+---------+-------+--------+--------+
|      1 | 2019-10-01 |     200 |     0 |    100 |    300 |
|      2 | 2019-10-02 |     200 |     0 |    100 |    400 |
|      3 | 2019-10-03 |     200 |   100 |      0 |    300 |
|      5 | 2019-10-03 |     200 |     0 |    200 |    500 |
|      4 | 2019-10-03 |     200 |   400 |      0 |    100 |
|      6 | 2019-10-04 |     200 |     0 |    100 |    200 |
|      7 | 2019-10-05 |     200 |   100 |      0 |    100 |
|      8 | 2019-10-05 |     200 |     0 |    400 |    500 |
+--------+------------+---------+-------+--------+--------+
8 rows in set (0.00 sec)

请注意，结果与您期望的结果略有不同，因为仅在需要时才会进行拍摄。