请教SQL语句写法

有表如下
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&gt; 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&gt;   
23\---------------------------------------------------------------   
24  
25SQL&gt; create table cc(id number,value number);   
26  
27Table created   
28  
29SQL&gt; insert into cc values(1,5);   
30  
311 row inserted   
32  
33SQL&gt; insert into cc values(2,7);   
34  
351 row inserted   
36  
37SQL&gt; insert into cc values(3,4);   
38  
391 row inserted   
40  
41SQL&gt; insert into cc values(4,6);   
42  
431 row inserted   
44  
45SQL&gt; select * from cc;   
46  
47ID VALUE   
48\---------- ----------   
491 5   
502 7   
513 4   
524 6   
53  
54以下利用分析函数:   
55SQL&gt; 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)))>
Published At
Categories with 数据库类
Tagged with
comments powered by Disqus