mysql - 根据上一行中的值计算 MariaDB 中的新值

Question

在 MariaDB 10.3 中，我有一个这样的表：

ID	日期	和
1	2020-01-01	120
1	2020-02-01	130
1	2020-03-01	140
1	2020-04-01	150
1	2020-05-01	160
1	2020-06-01	170

我需要在每个日期之后计算剩余的总和。像这样的东西。我需要将计算作为 MariaDB 中的查询进行。

ID	日期	和	还款前剩余总金额
1	2020-01-01	120	870
1	2020-02-01	130	750
1	2020-03-01	140	620
1	2020-04-01	150	480
1	2020-05-01	160	330
1	2020-06-01	170	170

我找到了一些接近的解决方案，但无法以适当的方式更改它们以满足我的需要。任何想法，将不胜感激。

最后一列的逻辑是：

1. 第一个值是欠款总额 (120+130+140...)
2. 每个下一个值都是根据该数字减去同一行的 sum 列中的值计算得出的，即

• 870-120=750；
• 750-130=620；
• 620-140=480；等等

我的尝试是：

1. 第一个接近但没有工作的查询：

    选择
    id,s.sum,s.date,
    @b := @b + s.sum 作为余额
    从
    (SELECT @b := 0.0) 假人
      交叉连接
        tpp AS
        其中 id=1
    订购方式
        s.日期；

结果是：

ID	日期	和	还款前剩余总金额
1	2020-01-01	120	120
1	2020-02-01	130	250
1	2020-03-01	140	380
1	2020-04-01	150	520
1	2020-05-01	160	670
1	2020-06-01	170	840

即它有点颠倒了结果。并用下一行的值增加值。

2. 另一个查询与 LAG 函数有关，但减法部分令人失望

    选择
    身份证，日期，
    sum(sum)-LAG(sum) OVER (ORDER BY date) AS l
    来自 tpp
    其中 id=1
    按日期分组，ID
    按日期订购

结果：

ID	日期	还款前剩余总金额
1	2020-01-01	无效的
1	2020-02-01	10
1	2020-03-01	10
1	2020-04-01	10
1	2020-05-01	10
1	2020-06-01	10

它减去：

• 130-120=10；
• 140-130=10；
• 150-140=10；等等

标签： mysqlsqlmariadbmariadb-10.3

Answer 1

您只需要 SUM() 窗口函数：

SELECT id, date,
       SUM(sum) OVER (ORDER BY date DESC) AS l
FROM tpp
WHERE id = 1
ORDER BY date

请参阅演示。
结果：

> id | date       |   l
> -: | :--------- | --:
>  1 | 2020-01-01 | 870
>  1 | 2020-02-01 | 750
>  1 | 2020-03-01 | 620
>  1 | 2020-04-01 | 480
>  1 | 2020-05-01 | 330
>  1 | 2020-06-01 | 170