sql 高手请进

[wlvcd]

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语句 （存储过程受限制）

[onejune4450]

SQL> select * from mm;

M1 V1
---------- ----------
2 30
5 90
7 50
8 80

SQL> 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

[onejune4450]

SQL> select m1,v1,v1-v2 from (
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

[onejune4450]

1 select a.m1,a.v1,a.v1 - nvl(b.v1,a.v1) from
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

[寒若辰]

SQL> select m1 month,v1 total,v1-v2 increase/Decrease 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