1.资料
table:
month total
2 30
5 90
7 50
8 80
运用sql语句实现
month total increase/Decrease
2 30 0
5 90 +60
7 50 -40
8 80 +30请问怎么写sql语句 (存储过程受限制)
table:
month total
2 30
5 90
7 50
8 80
运用sql语句实现
month total increase/Decrease
2 30 0
5 90 +60
7 50 -40
8 80 +30请问怎么写sql语句 (存储过程受限制)
---------- ----------
2 30
5 90
7 50
8 80SQL> select m1,v1,v1-v2 from (
2 select m1,v1,lag(v1,1,0) over(order by m1) v2 from mm )
3 / M1 V1 V1-V2
---------- ---------- ----------
2 30 30
5 90 60
7 50 -40
8 80 30
2 select m1,v1,lag(v1,1,v1) over(order by m1) v2 from mm ); M1 V1 V1-V2
---------- ---------- ----------
2 30 0
5 90 60
7 50 -40
8 80 30
2 (select rownum rn,m1,v1 from mm order by m1) a,
3 (select rownum rn,m1,v1 from mm order by m1) b
4* where a.rn -1 = b.rn(+)
SQL> / M1 V1 A.V1-NVL(B.V1,A.V1)
---------- ---------- -------------------
2 30 0
5 90 60
7 50 -40
8 80 30
2 select m1,v1,lag(v1,1,0) over(order by m1) v2 from mm )
3 / M1 V1 V1-V2
---------- ---------- ----------
2 30 30
5 90 60
7 50 -40
8 80 30