一定要描述的详细,不然人家怎么帮你 我的理解不知道正确不with t as ( select 1 id, 9 input,0 output from dual union all select 2,0,2 from dual union all select 3,9,0 from dual union all select 4,6,8 from dual ) select t.input,t.output,sum(t.input-t.output) over(order by id) storage from t
贴下结果SQL> with t as ( 2 select 1 id, 9 input,0 output from dual 3 union all select 2,0,2 from dual 4 union all select 3,9,0 from dual 5 union all select 4,6,8 from dual 6 ) 7 select t.input,t.output,sum(t.input-t.output) over(order by id) storage 8 from t; INPUT OUTPUT STORAGE ---------- ---------- ---------- 9 0 9 0 2 7 9 0 16 6 8 14
描述我看懂了: 第一行的C1=A-B 第二行的C2=C1+A-B 第三行的C3=C2+A-B 第四行的C4=C3+A-B 4楼的结果是对的。但是sum(t.input-t.output) over(order by id)看不太懂。希望解释一下。
4楼jym2002的用的是组内排序关于累加也可以使用自身连接的方法。例如: -----先创建临时表,两个字段,分别为 A ,B------- with test as ( select 9 A,0 B from dual union all select 0,2 from dual union all select 9,0 from dual union all select 6,8 from dual ) select A,B,(select sum(A-B) from test where rownum <= a.rw ) as C from (select A,B,rownum as rw from test ) a;
我的理解不知道正确不with t as (
select 1 id, 9 input,0 output from dual
union all select 2,0,2 from dual
union all select 3,9,0 from dual
union all select 4,6,8 from dual
)
select t.input,t.output,sum(t.input-t.output) over(order by id) storage
from t
2 select 1 id, 9 input,0 output from dual
3 union all select 2,0,2 from dual
4 union all select 3,9,0 from dual
5 union all select 4,6,8 from dual
6 )
7 select t.input,t.output,sum(t.input-t.output) over(order by id) storage
8 from t; INPUT OUTPUT STORAGE
---------- ---------- ----------
9 0 9
0 2 7
9 0 16
6 8 14
第一行的C1=A-B
第二行的C2=C1+A-B
第三行的C3=C2+A-B
第四行的C4=C3+A-B
4楼的结果是对的。但是sum(t.input-t.output) over(order by id)看不太懂。希望解释一下。
-----先创建临时表,两个字段,分别为 A ,B-------
with test as (
select 9 A,0 B from dual
union all select 0,2 from dual
union all select 9,0 from dual
union all select 6,8 from dual
)
select A,B,(select sum(A-B) from test where rownum <= a.rw ) as C
from (select A,B,rownum as rw from test ) a;