查询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

尝试这个：

with tab as (select l.*,
                    dense_rank() over (partition by l.account_no order by l.gdate) min_date
               from ledger l),
tab2 as (select tab.*, 
                sum(case when min_date <> 1 then debit else 0 end) over (partition by account_no) t_debit,
                sum(case when min_date <> 1 then credit else 0 end) over (partition by account_no) t_credit
           from tab)
select ACCOUNT_NO , 
       DEBIT as opening, 
       T_DEBIT as DEBIT, 
       T_CREDIT as CREDIT, 
       (DEBIT + T_DEBIT - T_CREDIT) closing
  from tab2
 where min_date = 1;

ouput：

| ACCOUNT_NO | OPENING | T_DEBIT | T_CREDIT | CLOSING |
|------------|---------|---------|----------|---------|
|    10-0001 |    1000 |       0 |      440 |     560 |
|    20-0001 |    3000 |    1200 |        0 |    4200 |
|    30-0001 |     300 |     500 |     1340 |    -540 |

但是，在您的插图中，最后一个帐户的关闭为540，但应为-540

编辑：如果需要特定日期输出ID，则：

with tab as (select l.ACCOUNT_NO,
                    sum(case when gdate < '02-OCT-2013' then (debit - credit) else 0 end) opening,
                    sum(case when gdate >= '02-OCT-2013' then debit else 0 end) t_debit,
                    sum(case when gdate >= '02-OCT-2013' then credit else 0 end) t_credit
               from ledger l
              group by l.ACCOUNT_NO)
select ACCOUNT_NO , 
       opening, 
       T_DEBIT as debit, 
       T_CREDIT as credit, 
       (opening + T_DEBIT - T_CREDIT) closing
  from tab
order by 1;

Answer 2

尝试这个...

SELECT x.*, (x.Opening + x.Debit - x.Credit) as "CLOSING"
FROM (
    SELECT l.ACCOUNT_NO as "ACCOUNT_NO",
        (SELECT DEBIT FROM ledger l2 
         WHERE l2.ACCOUNT_NO = l.ACCOUNT_NO AND l2.gdate < '02/10/2013') as "OPENING"
        SUM(l.DEBIT) as "DEBIT", SUM(l.CREDIT) as "CREDIT" 
    FROM ledger as l
    GROUP BY l.ACCOUNT_NO
) as x