sql - 如何从交易表中获取余额
问题描述
我的 postgresql 服务器上有这样的事务表
CREATE TABLE tbl_transaction
(
id serial NOT NULL,
id_coa integer,
date_trx date,
amount numeric(15,2),
dc character varying(1)
);
还有这样的数据
INSERT INTO tbl_transaction (id, id_coa, amount, date_trx, dc)
VALUES
(1, 1, 100, '2020-01-31', 'D'),
(2, 2, 100, '2020-01-31', 'D'),
(3, 3, 100, '2020-01-31', 'D'),
(4, 1, 100, '2020-02-01', 'D'),
(5, 1, 50, '2020-02-01', 'C'),
(6, 1, 200, '2020-02-02', 'D'),
(7, 2, 150, '2020-02-01', 'D'),
(8, 2, 70, '2020-02-01', 'C'),
(9, 2, 120, '2020-02-02', 'D'),
(10, 3, 200, '2020-02-01', 'D'),
(11, 3, 120, '2020-02-01', 'C'),
(12, 3, 130, '2020-02-02', 'D');
从上面的数据中,如果我从 2020-02-01 查询到 2020-02-02,我想要如下结果:
| id | id_coa | date_trx |beginning_balance| debit | credit |
| --- | ------ | ---------- |-----------------| ------ | ------ |
| 4 | 1 | 2020-02-01 | 100.00 | 100.00 | 0 |
| 5 | 1 | 2020-02-01 | 200.00 | 0 | 50.00 |
| 6 | 1 | 2020-02-02 | 150.00 | 200.00 | 0 |
| 7 | 2 | 2020-02-01 | 100.00 | 150.00 | 0 |
| 8 | 2 | 2020-02-01 | 250.00 | 0 | 70.00 |
| 9 | 2 | 2020-02-02 | 180.00 | 120.00 | 0 |
| 10 | 3 | 2020-02-01 | 100.00 | 200.00 | 0 |
| 11 | 3 | 2020-02-01 | 300.00 | 0 | 120.00 |
| 12 | 3 | 2020-02-02 | 180.00 | 130.00 | 0 |
我的查询如下:
SELECT
jc.id,
jc.id_coa,
jc.date_trx,
CASE
WHEN jc.dc = 'D' THEN
jc.amount
ELSE 0
END AS debit,
CASE
WHEN dc = 'C' THEN
jc.amount
ELSE 0
END AS credit
FROM tbl_transaction jc
WHERE jc.date_trx >= '2020-02-01'
AND jc.date_trx <= '2020-02-02'
ORDER BY jc.id_coa ASC
我的问题是:
如何获得 begin_balance?
beginning_balance = SUM(debit-credit) before '2020-02-01' + debit on date_trx
或者beginning_balance = SUM(debit-credit) before '2020-02-01' - credit on date trx
解决方案
使用子查询使用窗口函数计算余额。然后过滤外部查询:
select *
from (select id, id_coa, date_trx,
(sum(case when dc = 'C' then -amount else amount end) over (partition by id_coa order by id) -
(case when dc = 'C' then -amount else amount end)
) as balance,
(case when dc = 'C' then amount else 0 end) as credit,
(case when dc = 'D' then amount else 0 end) as debit
from tbl_transaction t
) t
where t.date_trx >= '2020-02-01' and t.date_trx <= '2020-02-02';
这是一个 db<>fiddle。
顺便说一句,减去贷方并增加借方,这很奇怪。
推荐阅读
- python - 赋值前引用的局部变量 | matplotlib
- angular - NestJS 服务器上的 Angular PWA 在新部署后崩溃
- python - 'float' 对象没有属性 'round'
- docker - Kaniko 中构建的容器目录权限不好,但在 Docker Desktop 中构建时可以工作
- api - 盈透证券API投资数量
- html - 在我的页面上实现 css '卡片显示'不起作用
- powershell - 尝试将此字符串中的数字数值转换为百分比。有没有简单的方法在powershell中做到这一点?
- java - 为什么当多个线程使用迭代器同时在同一个数组列表上工作时此代码不会抛出 ConcurrentModificationException
- node.js - 从 TypeScript Azure 函数调用 MS Power Automate
- domain-driven-design - 需要关于域模型中的抽象和更新过程的意见