时间戳

首页 > 解决方案 > MySQL从前几行添加余额

mysql - MySQL从前几行添加余额

问题描述

我已经尝试了一些我在这里看到的东西，但在我的情况下它不起作用，每一行的余额似乎重复。

无论如何，我有一个保存抵押交易的表，该表有一个存储利息附加值或支付值的列。

所以我可能有：

Balance: 100,000
Interest added 100 - balance 100,100
Payment made -500 - balance 99,600

Interest added 100 - balance 99,700
Payment made -500 - balance 99,200

我正在寻找的是一个查询，以最新的日期顺序提取所有这些，并根据它是否有利息或付款（不会为空的那个）将余额汇总在一列中，所以在最后它将承担当前责任的行

我不记得我尝试过的查询是什么，但它最终重复了行并且余额很奇怪

在此处输入图像描述

样本结构和数据：

CREATE TABLE account(
id int not null primary key auto_increment,
account_name varchar(50),
starting_balance float(10,6)

);

CREATE TABLE account_transaction(
id int not null primary key auto_increment,
account_id int NOT NULL,
date datetime,
interest_amount int DEFAULT null,
payment_amount float(10,6) DEFAULT NULL

);

INSERT INTO account (account_name,starting_balance) VALUES('Test Account','100000');

INSERT INTO account_transaction (account_id,date,interest_amount,payment_amount) VALUES(1,'2020-10-01 00:00:00',300,null);

INSERT INTO account_transaction (account_id,date,interest_amount,payment_amount) VALUES(1,'2020-10-01 00:00:00',null,-500);

INSERT INTO account_transaction (account_id,date,interest_amount,payment_amount) VALUES(1,'2020-11-01 00:00:00',300,null);

INSERT INTO account_transaction (account_id,date,interest_amount,payment_amount) VALUES(1,'2020-11-05 00:00:00',-500,null);

因此，利息将添加到滚动余额中，并且起始余额存储在帐户中 - 如果我们必须为此添加交易，那么可以。然后，当添加付款时，它可以是负数或正数，以减少移动到每一行的余额。

所以上面的例子我希望看到一些类似的东西：

在此处输入图像描述

我希望这会让它更清楚

标签： mysql

解决方案

WITH
starting_dates AS ( SELECT id account_id, MIN(`date`) startdate
                    FROM account_transaction 
                    GROUP BY id ),
combine AS ( SELECT 0 id, 
                    starting_dates.account_id, 
                    starting_dates.startdate `date`, 
                    0 interest_amount, 
                    account.starting_balance payment_amount
             FROM account
             JOIN starting_dates ON account.id = starting_dates.account_id
             UNION ALL
             SELECT id,
                    account_id, 
                    `date`, 
                    interest_amount, 
                    payment_amount
             FROM account_transaction )
SELECT DATE(`date`) `Date`,
       CASE WHEN interest_amount = 0     THEN 'Balance Brought Forward'
            WHEN payment_amount IS NULL  THEN 'Interest Added'
            WHEN interest_amount IS NULL THEN 'Payment Added'
            ELSE 'Unknown transaction type'
            END `Desc`,
       CASE WHEN interest_amount = 0 THEN ''
            ELSE COALESCE(interest_amount, 0)
            END Interest,
       COALESCE(payment_amount, 0) Payment,
       SUM(COALESCE(payment_amount, 0) + COALESCE(interest_amount, 0))
           OVER (PARTITION BY account_id ORDER BY id) Balance
FROM combine
ORDER BY id;

小提琴

PS。提供的源数据（带有 的行id=4）根据提供的所需输出进行了更改。源结构已更改，FLOAT(10,6)与提供的值不兼容已替换为DECIMAL.

聚苯乙烯。允许存在多个帐户。

推荐阅读