select time,
(lead (date)over(order by date)-date)*24*60 time
from t1;
具体思路是先通过lead函数把上下两行时间日期字段的时间差求出来在做其他运算,
现在的问题是怎么把计算得到的结果“time”插入到t1表中?
使用alter命令add失败,事先添加字段time再用update set time=也不行,希望高手能给小弟指点一下
(lead (date)over(order by date)-date)*24*60 time
from t1;
具体思路是先通过lead函数把上下两行时间日期字段的时间差求出来在做其他运算,
现在的问题是怎么把计算得到的结果“time”插入到t1表中?
使用alter命令add失败,事先添加字段time再用update set time=也不行,希望高手能给小弟指点一下
Table createdSQL> insert into t1(t_date) select sysdate +level from dual connect by level <=10;
10 rows insertedSQL> select * from t1;
T_DATE TIMEDIFF
----------- ----------
2015-06-06
2015-06-07
2015-06-08
2015-06-09
2015-06-10
2015-06-11
2015-06-12
2015-06-13
2015-06-14
2015-06-15
10 rows selectedSQL> update t1 b set b.timediff =
2 (select timediff from (select lead(t_date)over(order by t_date) - t_date as timediff from t1) a
3 where a.rowid=b.rowid);
10 rows updatedSQL> select * from t1;
T_DATE TIMEDIFF
----------- ----------
2015-06-06 1
2015-06-07 1
2015-06-08 1
2015-06-09 1
2015-06-10 1
2015-06-11 1
2015-06-12 1
2015-06-13 1
2015-06-14 1
2015-06-15
10 rows selected
如果数据量大的话,建议create table select ... + Rename
SQL> create table t2 as select t1.t_date, lead(t_date)over(order by t_date) - t_date as timediff from t1;
Table createdSQL> drop table t1;
Table droppedSQL> alter table t2 rename to t1;
Table alteredSQL> select * from t1;
T_DATE TIMEDIFF
----------- ----------
2015-06-06 1
2015-06-07 1
2015-06-08 1
2015-06-09 1
2015-06-10 1
2015-06-11 1
2015-06-12 1
2015-06-13 1
2015-06-14 1
2015-06-15
10 rows selected