根据贷方和借方记录计算余额

Question

我的数据库表中有以下记录：

Date            Credit  Debit      Description
--------------- ------- -------    ---------------
12-24-2015      5                  Purchased credit
12-20-2015              1          Consumed credit
12-15-2015      3                  Purchased credit
12-08-2015              1          Consumed credit
12-08-2015              1          Consumed credit
12-07-2015              1          Consumed credit
12-04-2015              1          Consumed credit
12-03-2015              1          Consumed credit
12-01-2015      5                  Purchased credit

我想计算并显示每个记录的余额，如下所示：

Date         Credit  Debit   Balance  Description
------------ ------- ------- -------  ---------------
12-24-2015   5       0       7        Purchased credit
12-20-2015           1       2        Consumed credit
12-15-2015   3       0       3        Purchased credit
12-08-2015           1       0        Consumed credit
12-08-2015           1       1        Consumed credit
12-07-2015           1       2        Consumed credit
12-04-2015           1       3        Consumed credit
12-03-2015           1       4        Consumed credit
12-01-2015   5       0       5        Purchased credit

有人能帮助我达到上述结果吗？

Answer 1

要生成平衡，请在分析版本中使用sum()。

select tdate, credit, debit, 
       sum(nvl(credit, 0)-nvl(debit, 0)) over (order by rn) balance, description
  from (
    select tdate, credit, debit, row_number() over (order by tdate) rn, description 
      from test)
  order by rn desc

如果表中包含递增主键，则可以使用该主键而不是生成的行号。

测试数据和输出：

create table test (tdate date, credit number(6), debit number(6), description varchar2(20));
insert into test values (date '2015-12-24', 5, null, 'Purchased credit');
insert into test values (date '2015-12-20', null, 1, 'Consumed credit');
insert into test values (date '2015-12-15', 3, null, 'Purchased credit');
insert into test values (date '2015-12-08', null, 1, 'Consumed credit');
insert into test values (date '2015-12-08', null, 1, 'Consumed credit');
insert into test values (date '2015-12-07', null, 1, 'Consumed credit');
insert into test values (date '2015-12-04', null, 1, 'Consumed credit');
insert into test values (date '2015-12-03', null, 1, 'Consumed credit');
insert into test values (date '2015-12-01', 5, null, 'Purchased credit');
TDATE        CREDIT   DEBIT    BALANCE DESCRIPTION
----------- ------- ------- ---------- --------------------
2015-12-24        5                  7 Purchased credit
2015-12-20                1          2 Consumed credit
2015-12-15        3                  3 Purchased credit
2015-12-08                1          0 Consumed credit
2015-12-08                1          1 Consumed credit
2015-12-07                1          2 Consumed credit
2015-12-04                1          3 Consumed credit
2015-12-03                1          4 Consumed credit
2015-12-01        5                  5 Purchased credit

Answer 2

您应该能够为此使用LAG分析函数来查看前一行的数据。

SELECT Date, 
       Credit, 
       Debit, 
       LAG(Balance, 1, 0) OVER(ORDER BY Date) - Debit + Credit AS Balance, 
       Description
  FROM sometable

1参数意味着它看起来是前一行，0参数意味着如果给定偏移量的行不存在，它将返回0（即第一行）。

来源：https://oracle-base.com/articles/misc/lag-lead-analytic-functions

Answer 3

试试这个，首先进行自联接，然后按以下顺序列出结果：

SELECT a1.Date, a1.Debit, a1.Credit, SUM(a2.Debit-a2.Credit) Balance, Description
FROM table a1, table a2
WHERE a1.debit<=a2.debit and a1.credit<=a2.credit OR (a1.debit=a2.debit and a1.credit=a2.credit and a1.date = a2.date)
GROUP BY a1.Date, a1.Debit, a1.Credit
ORDER BY a1.Date DESC;

Answer 4

对于mssql可能看到此问题的人，您可以使用以下查询：

select tdate, credit, debit, 
       sum(isnull(credit, 0)-isnull(debit, 0)) over (order by rn) balance, description
  from (
    select tdate, credit, debit, row_number() over (order by tdate) rn, description 
      from test)
  order by rn desc