1000, 100,
50 fifty
20 to 30
10 20
5 to 15

Consult the great god, and the second column of the previous line value minus the value of the first column of the next line to check for the next line of the second column values, such as: the second column the first line the value of 100 - the first column value is 50, the results for the second column in the second row value 50
The second column in the second row value is 50 - the value of the first column of the third line 20, the results for the second column of the third line value 30
The second column in the third row value of 30 - the first column of the fourth row 10, the results for the fourth row of the second column value 20

So pushed down

Could you tell me how to query the, thank you very much!

CodePudding user response:

The sum (col) over (order by id)

CodePudding user response:

An SQL statement seems hard to get, need to use a cursor,

CodePudding user response:

With TMP as
(select serial number 1, 1000 a, 100 b from dual
Union all select serial number 2, 50 a, 50 b from dual
Union all select serial number 3, 20 a, 30 b from dual
Union all select serial number 4, 10 a, 20 b from dual
Union all select serial number 5, 5 a, 15 b from dual
)
Select the serial number, a, b, (lead over (b) (order by serial number desc) - a) c from TMP
The order by serial number;

Maybe this way, the column name for the column "c" (equal value with the first "b")
The algorithm of the second column is

CodePudding user response:

If you now have two columns of data, then you what is the purpose of such a query?

CodePudding user response:

The first column refers to the consumption amount, each time the second column results is refers to the balance

CodePudding user response:

Is I didn't describe clearly, the value of the first column is known, and the second column in addition to the value of the first line is know, the other is identified

CodePudding user response:

With TMP as
(select id 1, 1000 a, 100 b from dual
Union all select 2 id, 50 a, null b from dual
Union all select 3 id, 20 a, null b from dual
Union all select 4 id, 10 a, null b from dual
Union all select 5 id, 5 a, null b from dual
)
Select id, type a, b, the from TMP where id=1
Union all
Select id, a, b (select from TMP where id=1) - the sum (b) over (order by id rows between unbounded preceding and current row)
The from TMP where id> 1;

CodePudding user response:

refer to 7th floor mayanzs response:

with TMP as
(select id 1, 1000 a, 100 b from dual
Union all select 2 id, 50 a, null b from dual
Union all select 3 id, 20 a, null b from dual
Union all select 4 id, 10 a, null b from dual
Union all select 5 id, 5 a, null b from dual
)
Select id, type a, b, the from TMP where id=1
Union all
Select id, a, b (select from TMP where id=1) - sum (b) over (order by id rows between unbounded preceding and current row)
The from TMP where id> 1;

To put the red sum (b) sum (a), other unchanged

CodePudding user response:

I'm sorry, a slip of the pen,

CodePudding user response:

As with t (
Select 1000 as c1, 100 as c2 from dual
Union all select 50 as c1, 50 as c2 from dual
30 as union all select 20 as c1, c2 from dual
Union all select 10 as c1, 20 as c2 from dual
15 as union all select 5 as c1, c2 from dual
)
Select * from t. (
Select rownum as rw, t. * from t) t left the join (select rownum - 1 as rw, t. * from t) t1 on t1. The rw=t.r w
Where tc 2 - t1. C1=t1. C2