TABLE中加入了
2000/3/5 60 10
是变成这
日期 收入 支出
2000/3/1 50 30
2000/3/2 45 60
2000/3/5 60 10
2000/3/5 60 10
还是这
2000/3/1 50 30
2000/3/2 45 60
2000/3/5 120 20
2000/3/5 60 10
是变成这
日期 收入 支出
2000/3/1 50 30
2000/3/2 45 60
2000/3/5 60 10
2000/3/5 60 10
还是这
2000/3/1 50 30
2000/3/2 45 60
2000/3/5 120 20
日期 收入 支出
2000/3/1 50 30
2000/3/2 45 60
2000/3/5 60 10
2000/3/5 60 10
然后
create view b as
select acc_day,sum(debit),sum(credit)
from a
group by acc_day;
select acc_day,debit,credit,debit-credit+surplus from (
select acc_day,debit,credit,
lag(debit-credit,1,0) over(order by acc_day) surplus
from b )
create view b(acc_day,debitas,credit)
select acc_day,sum(debit),sum(credit)
from a
group by acc_day;就这样了,不会错的。
2000/3/1 50 30 20
2000/3/2 45 60 5
2000/3/5 60 10 35
select thedate,income,outgo,
lag(surplus+income-outgo,1,0) over(order by thedate) surplus0
from(
select thedate,income,outgo,
lag(income-outgo,1,0) over(order by thedate) surplus
from inout)
)
這是我寫出來的,但是數據多余三行就有問題了,哪位大人指點一下應該怎麼改
--------- ---------- ----------
01-MAR-00 50 30
02-MAR-00 45 60
05-MAR-00 60 10
05-MAR-00 60 10SQL> select every_thedate,sum_income,sum_outgo,add_surplus
2 from (select every_thedate,nvl(sum(income),0) sum_income,nvl(sum(outgo),0) sum_outgo,
3 sum(nvl(sum(income),0)-nvl(sum(outgo),0)) over(order by every_thedate) add_surplus
4 from (select nvl((select min(thedate) from inout ),sysdate) + rownum -1 every_t
hedate
5 from emp where rownum <= nvl((select max(thedate) - min(thedate)
6 from inout ),sysdate) + 1 ) a,
7 inout b
8 where a.every_thedate = b.thedate(+)
9 group by every_thedate)
10 /EVERY_THE SUM_INCOME SUM_OUTGO ADD_SURPLUS
--------- ---------- ---------- -----------
01-MAR-00 50 30 20
02-MAR-00 45 60 5
03-MAR-00 0 0 5
04-MAR-00 0 0 5
05-MAR-00 120 20 105
select thedate,income,outgo,(income-outgo+surplus1)remain from (
select thedate,income,outgo,
lag(surplus0+income-outgo,1,0) over(order by thedate) surplus1
from(
select thedate,income,outgo,
lag(surplus+income-outgo,1,0) over(order by thedate) surplus0
from(
select thedate,income,outgo,
lag(suminout,1,0) over(order by thedate) surplus
from (select thedate,sum(income) income,sum(outgo) outgo,(sum(income)-sum(outgo))suminout from inout group by thedate))
))
THEDATE INCOME OUTGO REMAIN
---------- ---------- ---------- ----------
01-3月 -00 50 30 20
02-3月 -00 45 60 5
05-3月 -00 120 20 105
select thedate,sum(income),sum(outgo),
sum(income-outgo) over(partition thedate order by thedate) surplus
from inout b
where group by thedate
select thedate,income,outgo,
sum(income-outgo) over(order by thedate) surplus
from inout b
rownum <= nvl((select max(thedate) - min(thedate) from inout ),sysdate) + 1,
--------------------------------------
改为:
rownum <= nvl((select max(thedate) - min(thedate) from inout ),0) + 1,
如果不要最后一天可改为:
rownum <= nvl((select max(thedate) - min(thedate) from inout ),0)
变为
select thedate,sum_income,sum_outgo,
sum(sum_income-sum_outgo) over(order by thedate) surplus
from ( select thedate,sum(income) sum_income,sum(outgo) sum_outgo from inout group by thedate)
rownum <= nvl((select max(thedate) - min(thedate) from inout ),-1) + 1,