Oracle查询以打开和关闭余额

Question

嗨，我有下表和数据以获得每个帐户开放余额所需的开放和关闭余额GDATE＆lt; 02-OCT-2013这是我的桌子

 create table ledger (account_no varchar2(10),gdate date,debit number(8),credit number(8))
insert into ledger (account_no,gdate,debit,credit) values ('10-0001','01-oct-2013',1000,0);
  insert into ledger (account_no,gdate,debit,credit) values ('10-0001','24-oct-2013',0,440);
insert into ledger (account_no,gdate,debit,credit) values ('20-0001','01-oct-2013',3000,0);
insert into ledger (account_no,gdate,debit,credit) values ('30-0001','01-oct-2013',300,0);
insert into ledger (account_no,gdate,debit,credit) values ('20-0001','16-oct-2013',1200,0);
insert into ledger (account_no,gdate,debit,credit) values ('30-0001','17-oct-2013',0,1340);
insert into ledger (account_no,gdate,debit,credit) values ('30-0001','24-oct-2013',500,0);

我需要以下结果

 ACCOUNT_NO OPENING  DEBIT  CREDIT  CLOSING
  10-0001    1000    0       440    560
  20-0001    3000    1200      0    4200
  30-0001     300    500    1340    540

Answer 1

这更多的是评论，因为它没有回答问题。您的查询简化为：

Select account_no, LAG(closing, 1 ,0) OVER (order by account_no) as opening,
       debit, credit,
       (LAG(closing,1,0) OVER (order by account_no )+ closing) as closing
from (select account_no, 0 as OPEN, SUM(debit) as debit, SUM(credit) as credit,
             sum(debit) - sum(credit) as closing
      FROM ledger
      where gdate > '20-oct-13'
      group by account_no
     ) a;

一些笔记。除非您在下一个外部查询中选择rownum，否则在子查询或CTE中的order by没有用。如果要订购的结果，则将order by放在外部查询中。

查询本身没有意义。为什么开设余额来自上一个帐号？通常，此类术语用于特定帐户上的日期，并且您已经消除了group by子句中的所有日期。

Answer 2

您一直在不断更改要求，但是根据目前所显示的内容，这有效：

select account_no,
 max(opening) keep (dense_rank first order by gdate) as opening,
 sum(debit) as debit,
 sum(credit) as credit,
 max(closing) keep (dense_rank first order by gdate desc) as closing
from (
 select account_no, gdate, credit, debit,
 lag(balance, 1, 0) over (partition by account_no order by gdate) as opening,
 balance as closing
 from (
  select account_no, gdate, debit, credit,
  sum(debit) over (partition by account_no order by gdate) as sum_debit,
  sum(credit) over (partition by account_no order by gdate) as sum_credit,
  sum(credit) over (partition by account_no order by gdate)
   - sum(debit) over (partition by account_no order by gdate) as balance
  from ledger
 )
)
where gdate > date '2013-10-02'
group by account_no
order by account_no;

当您有不同的数据时，这与我之前链接到的逻辑相同。sql小提琴。

我不确定为什么您表现出您的主机余额是积极的；当您拥有的只是借方时，似乎是错误的。如果您确实想要的话，请交换如何计算余额：

...
  sum(debit) over (partition by account_no order by gdate)
   - sum(credit) over (partition by account_no order by gdate) as balance
...

sql小提琴。