有表如下
id value
1 5
2 7
3 4
4 6
要求如下结果
1 5 null
2 7 0.4
3 4 -0.429
4 6 0.5
其中第三列数字是本行第二列减去上行第二列然后除以上行第二列
---------------------------------------------------------------
8i下调试成功:
select id,value,(value-(select value from 你的表 where id=(select max(id) from 你的表 where id
1<bb.id))) (="" (select="" )="" -="" 11:08:03="" \---------------------------------------------------------------="" a,="" a.id="b.row_no(+)" a.id,="" a.row_no="b.row_no(+)" a.value,="" aa="" b="" b.value)="" bb;="" decode(b.value,null,null,(a.value="" from="" id="(select" id+1="" id<bb.id))="" max(id)="" row_no,="" rownum="" rownum+1="" select="" sql="" value="" where="" yourtable="" yourtable)="" yourtable.*="" 不知道你的上一行是指什么,我这个sql是按照rownum来确定上一行的,="" 你的表="" 如果是按照id来确定上一行=""> select * from tcn;
2
3AAA BBB
4\---------- ---------
51 5
62 7
73 4
84 6
9
10实际:71
1111:08:23 SQL> select tcn.aaa,tcn.bbb,(tcn.bbb-tcnb.bbb)/tcnb.bbb from tcn
1211:08:27 2 ,(select aaa+1 aaa,bbb from tcn) tcnb where tcn.aaa=tcnb.aaa(+);
13
14AAA BBB (TCN.BBB-TCNB.BBB)/TCNB.BBB
15\---------- --------- ---------------------------
161 5
172 7 .4
183 4 -.4285714
194 6 .5
20
21实际:70
2211:08:27 SQL>
23\---------------------------------------------------------------
24
25SQL> create table cc(id number,value number);
26
27Table created
28
29SQL> insert into cc values(1,5);
30
311 row inserted
32
33SQL> insert into cc values(2,7);
34
351 row inserted
36
37SQL> insert into cc values(3,4);
38
391 row inserted
40
41SQL> insert into cc values(4,6);
42
431 row inserted
44
45SQL> select * from cc;
46
47ID VALUE
48\---------- ----------
491 5
502 7
513 4
524 6
53
54以下利用分析函数:
55SQL> select id,value,(value-(lag(value,1,null) over(order by id)))/(lag(value,1,null) over(order by id)) from cc order by id;
56
57ID VALUE (VALUE-(LAG(VALUE,1,NULL)OVER(
58\---------- ---------- ------------------------------
591 5
602 7 0.4
613 4 -0.428571428571429
624 6 0.5</bb.id)))>