首页 > 解决方案 > 如何从交易表中获取余额

问题描述

我的 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?

标签: sqlpostgresqlpostgresql-9.3

解决方案


使用子查询使用窗口函数计算余额。然后过滤外部查询:

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。

顺便说一句,减去贷方并增加借方,这很奇怪。


推荐阅读