如何从现有余额中获取新的运行余额? [英] How to get the New Running Balance from Existing Balance?

问题描述

这是我的sql和查询的输出...

Here's my sql and Output of my query...

sql:

SELECT
id ID, 
token TK, 
actual_pay PAY,
IF(@rtp IS NULL, @rtp:=token, @rtp:=@bal+actual_pay) RTP,
IF(@bal IS NULL, @bal:=actual_pay-token, @bal:=@rtp-token) BAL
FROM token_table a
JOIN (SELECT @rtp:=NULL, @bal:=NULL) b;

输出:

+----+------+-----+------+------+
| ID | TK   | PAY | RTP  | BAL  |
+----+------+-----+------+------+
|  1 | 500  | 900 |  500 |  400 |
|  2 | 1200 | 900 | 1300 |  100 |
|  3 | 900  | 900 | 1000 |  100 |
|  4 | 900  | 900 | 1000 |  100 |
|  5 | 400  | 900 | 1000 |  600 |
|  6 | 300  | 900 | 1500 | 1200 |
|  7 | 500  | 900 | 2100 | 1600 |
|  8 | 1700 | 900 | 2500 |  800 |
|  9 | 1800 | 900 | 1700 | -100 |
| 10 | 800  | 900 |  800 |    0 |
| 11 | 900  | 900 |  900 |    0 |
| 12 | 0    | 850 |  850 |  850 |
+----+------+-----+------+------+

这是我想要的输出...

Here's the output that I want to get...

问题:
1. stat字段的公式为:如果BAL(来自ID = 1)的值小于或等于TK(来自ID = 2)的值，如果是，则值应为1，否则值为0. .

2. nbal字段的公式为:如果BAL(来自ID = 1)的值小于或等于TK(来自ID = 2)的值，如果是，则该值应为0，否则应为BAL (来自ID = 1)减去TK(来自ID = 2).

3. ntk字段的公式为:如果BAL(来自ID = 1)的值小于或等于TK(来自ID = 2)的值，如果是，则该值应为TK(来自ID = 2)减去BAL (来自ID = 1)，否则值应为BAL(来自ID = 1)减去TK(来自ID = 2).

Problem:
1. The formula of stat field is: If value of BAL(from ID=1) is less than or equal to value of TK(from ID=2), if yes the value should be 1, if else the value should be 0.

2. The formula of nbal field is:If value of BAL(from ID=1) is less than or equal to value of TK(from ID=2), if yes the value should be 0, if else the value should be BAL(from ID=1) minus TK(from ID=2).

3. The formula of ntk field is: If value of BAL(from ID=1) is less than or equal to value of TK(from ID=2), if yes the value should be TK(from ID=2) minus BAL(from ID=1), if else the value should be BAL(from ID=1) minus TK(from ID=2).

+----+------+-----+------+------+------+------+------+
| ID | TK   | PAY | RTP  | BAL  | stat | nbal | ntk  |
+----+------+-----+------+------+------+------+------+
|  1 | 500  | 900 |  500 |  400 |    1 | 0    | 0    |
|  2 | 1200 | 900 | 1300 |  100 |    1 | 0    | 800  |
|  3 | 900  | 900 | 1000 |  100 |    1 | 0    | 800  |
|  4 | 900  | 900 | 1000 |  100 |    1 | 0    | 800  |
|  5 | 400  | 900 | 1000 |  600 |    0 | 300  | 300  |
|  6 | 300  | 900 | 1500 | 1200 |    0 | 700  | 0    |
|  7 | 500  | 900 | 2100 | 1600 |    1 | 0    | 0    |
|  8 | 1700 | 900 | 2500 |  800 |    1 | 0    | 100  |
|  9 | 1800 | 900 | 1700 | -100 |    1 | 0    | 1000 |
| 10 | 800  | 900 |  800 |    0 |    1 | 0    | 900  |
| 11 | 900  | 900 |  900 |    0 |    1 | 0    | 900  |
| 12 | 0    | 850 |  850 |  850 |    0 | 850  | 0    |
+----+------+-----+------+------+------+------+------+

推荐答案

将left join添加到下一行:

select a.id, a.tk, a.pay, a.rtp, a.bal, a.stat, a.nbal, a.ntk
from (
  select a.id, a.token tk, a.actual_pay pay,
    if(@rtp is null, @rtp:=a.token, @rtp:=@bal+a.actual_pay) rtp,
    ifnull(abs(@bal-a.token),0) ntk,
    if(@bal is null, @bal:=a.actual_pay-a.token, @bal:=@rtp-a.token) bal,
    @bal <= ifnull(c.token,0) stat,
    greatest(0, @bal-ifnull(c.token,0)) nbal
  from records a
  join (select @rtp:=null, @bal:=null) b
  left join records c on a.id = c.id-1) a;

小提琴

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