如何在sqlite中获得每个记录的余额

3z6pesqy 于 2022-11-30 发布在 SQLite

关注(0)|答案(1)|浏览(125)

我有一个名为transaction_table的表，因此我编写了以下代码

select 
    t._id, 
    t.description, 
    t.debit, 
    t.credit,
    (t.debit - t.credit) blnc,
    t.curr_id, 
    t.cus_id 
from transaction_table t

结果是：

this col
_id  description             debit    credit     blnc   curr_id   cuss_id
-------------------------------------------------------------------------
1    cr for customer 1       0.0     30.0      -30.0    1         1
2    cr for customer 1       0.0    500.0     -500.0    1         1
3    dbt for customer 1     70.0      0.0       70.0    1         1
4    cr for customer 2       0.0    600.0     -600.0    1         2
5    dat for customer 2    100.0      0.0      100.0    1         2
6    dat for customer 2    300.0      0.0      300.0    1         2

但我想要这样东西：

_id  description             debit    credit     blnc   curr_id   cuss_id
-------------------------------------------------------------------------
1    cr for customer 1       0.0     30.0      -30.0    1         1
2    cr for customer 1       0.0    500.0     -530.0    1         1
3    dbt for customer1      70.0      0.0      470.0    1         1
4    cr for customer 2       0.0    600.0     -600.0    1         2
5    dat for customer 2    100.0      0.0     -500.0    1         2
6    dat for customer 2    300.0      0.0     -200.0    1         2

那么，如何用sqlite实现第二个表呢？谢谢。

sqlite

来源：https://stackoverflow.com/questions/74617849/how-to-get-balance-per-recored-in-sqlite

1条答案

按热度按时间

jc3wubiy1#

使用SUM()窗口函数：

SELECT _id, description, debit, credit,
       SUM(debit - credit) OVER (PARTITION BY curr_id, cus_id ORDER BY _id) blnc,
       curr_id, cus_id 
FROM transaction_table;

根据您的要求，您可以从PARTITION BY子句中删除curr_id列。
对于不支持窗口函数的SQLite 3.25之前的版本，请使用自连接和聚合：

SELECT t1._id, t1.description, t1.debit, t1.credit,
       SUM(t2.debit - t2.credit) blnc,
       t1.curr_id, t1.cus_id
FROM transaction_table t1 INNER JOIN transaction_table t2
ON t2.curr_id = t1.curr_id AND t2.cus_id = t1.cus_id AND t2._id <= t1._id
GROUP BY t1._id;

根据您的要求，您可以从ON子句中删除条件t2.curr_id = t1.curr_id。
请参阅demo。

赞(0）回复(0）举报 2022-11-30

我来回答

如何在sqlite中获得每个记录的余额

1条答案

相关问题

热门标签

最新问答