当ID=1时,怎么算呢? 当ID!=1时, select ..... from table1 a,table1 b where a.id=b.(id+1)
select id,value,(value-(select value from dali.test2 where id=(select max(id) from dali.test2 where id<bb.id)))/(select value from dali.test2 where id=(select max(id) from dali.test2 where id<bb.id)) aa from dali.test2 bb;
8i下调试成功:select id,value,(value-(select value from 你的表 where id=(select max(id) from 你的表 where id<bb.id)))/(select value from 你的表 where id=(select max(id) from 你的表 where id<bb.id)) aa from 你的表 bb;
不知道你的上一行是指什么,我这个SQL是按照rownum来确定上一行的, select a.id, a.value, decode(b.value,NULL,null,(a.value - b.value)/b.value) from (select rownum row_no, yourtable.* from yourtable) a, ( select rownum+1 row_no, value from yourtable ) b where a.row_no = b.row_no(+)如果是按照id来确定上一行 select a.id, a.value, decode(b.value,NULL,null,(a.value - b.value)/b.value) from yourtable a, ( select id+1 row_no, value from yourtable ) b where a.id = b.row_no(+)
SQL> create table cc(id number,value number);Table createdSQL> insert into cc values(1,5);1 row insertedSQL> insert into cc values(2,7);1 row insertedSQL> insert into cc values(3,4);1 row insertedSQL> insert into cc values(4,6);1 row insertedSQL> select * from cc; ID VALUE ---------- ---------- 1 5 2 7 3 4 4 6以下利用分析函数: SQL> 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; ID VALUE (VALUE-(LAG(VALUE,1,NULL)OVER( ---------- ---------- ------------------------------ 1 5 2 7 0.4 3 4 -0.428571428571429 4 6 0.5
当ID!=1时,
select .....
from table1 a,table1 b
where a.id=b.(id+1)
select a.id, a.value, decode(b.value,NULL,null,(a.value - b.value)/b.value)
from (select rownum row_no, yourtable.* from yourtable) a, ( select rownum+1 row_no, value from yourtable ) b
where a.row_no = b.row_no(+)如果是按照id来确定上一行
select a.id, a.value, decode(b.value,NULL,null,(a.value - b.value)/b.value)
from yourtable a, ( select id+1 row_no, value from yourtable ) b
where a.id = b.row_no(+)
---------- ---------
1 5
2 7
3 4
4 6实际:71
11:08:23 SQL> select tcn.aaa,tcn.bbb,(tcn.bbb-tcnb.bbb)/tcnb.bbb from tcn
11:08:27 2 ,(select aaa+1 aaa,bbb from tcn) tcnb where tcn.aaa=tcnb.aaa(+);AAA BBB (TCN.BBB-TCNB.BBB)/TCNB.BBB
---------- --------- ---------------------------
1 5
2 7 .4
3 4 -.4285714
4 6 .5实际:70
11:08:27 SQL>
---------- ----------
1 5
2 7
3 4
4 6以下利用分析函数:
SQL> 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; ID VALUE (VALUE-(LAG(VALUE,1,NULL)OVER(
---------- ---------- ------------------------------
1 5
2 7 0.4
3 4 -0.428571428571429
4 6 0.5
支持Access,SQL Server,Sybase,Oracle.
中文版不用注册做好后联系我免费赠送
http://www.qwerks.com/download/6249/DBHelper.exe
id(1,2,4)那么以上几位兄弟就成立不了,建义用分折函数比较安全,而且性能绝对可取。
oracle数据库千变万化.