TABLE Date Revenue Expenditure 2000/3/1 50 302000/3/2 45 602000/3/5 60 10 Can I use the SELECT statement to get the following results?
Date income expenditure balance 2000/3/1 50 30 202000/3/3 0 0 52000/3/4 0 0 52000/3/5 60 10 55
This is a widely circulated topic. I used to say that this is Microsoft's test, this doesn't take it, let's see Oracle's solution.
SQL> Select * from test; AB C ---------- ---------- ---------- 1 1 02 2 14 4 26 1 38 4 4 Time: 00: 00: 00.00.00sql> Select TT1.TTA, TT1.TTTB, TT1.TTC, SUM (TT2.ttb) - SUM (TT2.ttc) Youwant2 from3 (4 SELECT T1.AA TTA, DECODE (T2 TTB, DECODE (T2.A, NULL, 0, T2.C) TTC5 from6 (SELECT (SELECT MIN (a) from test) ROWNUM - 1) AA from ALL_Objects 7 WHERE ROWNUM <= (SELECT MAX (a) - min (a) 1 from test)) T1, 8 test t29 where t1.aa = t2.a ( ) 10) TT1,11 (12 SELECT T1.AA TTA, DECODE (T2.A, NULL, 0, T2.B) TTB, DECODE (T2.A, NULL, 0, T2.C) TTC13 from 14 (SELECT (SELECT MIN (A) from test) ROWNUM - 1) AA From all_Objects 15 where rownum <= (SELECT MAX (A) - min (a) 1 from test)) T1, 16 test t217 where t1.aa = t2.a ( ) 18) TT219 WHERE TT1.TTA> = TT2 .tta, tt1.ttb, tt1.ttc; tttb ttc youwant -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- 1 1 0 12 2 1 23 0 0 24 4 2 45 0 0 46 1 3 27 0 0 28 4 4 2 Has selected 8 lines. Time: 00: 00: 00.00
816 above version using the simplified version of the Analistic Function SQL> Select * from test; AB C ---------------------------- 5 5 52 2 14 4 56 1 38 4 4SQL> SELECT TT2.TTA, TT2.TTB, TT2.TTC, SUM (TT2.ttb - tt2.ttc) Over (Order By Tt2.tta) Youwant2 from3 (4 Select T1.AA TTA, DECODE (T2.A, NULL, 0, T2.B) TTB, DECODE (T2.A, NULL, 0, T2.C) TTC5 from6 (SELECT (SELECT (SELECT MIN (a) from test) Rownum - 1 ) aa from all_Objects 7 where rownum <= (SELECT MAX (a) - min (a) 1 from test)) T1, 8 test t29 where t1.aa = t2.a ( ) 10) TT2; TTA TTB TTC youWant ---------- ---------------------------- 2 1 13 0 0 14 4 5 05 5 5 06 1 3 -27 0 0 -28 4 4 -2 Has selected 7 lines. SQL> In fact, this type of problem is a moon micro-form, mainly constructing a continuous digital collection, and if the number of all_Objects records can be used to make a self-connection. For example, there have been a number of days after seeking a certain period of time, such problems, is also a solution.
Related resources: with sound access new year fireworks animation effects - html-code
