没懂你的整点是什么意思,先写一下
创建表插入数据:
create table test
(name varchar2(10),
value int,
time date);
insert into test values ('张三',150,to_date('2017-09-11 12:01:01','yyyy-mm-dd hh24:mi:ss'));
insert into test values ('李四',280,to_date('2017-09-11 12:01:01','yyyy-mm-dd hh24:mi:ss'));
insert into test values ('老王',177,to_date('2017-09-11 12:01:01','yyyy-mm-dd hh24:mi:ss'));
insert into test values ('张三',170,to_date('2017-09-11 13:01:01','yyyy-mm-dd hh24:mi:ss'));
insert into test values ('李四',320,to_date('2017-09-11 13:01:01','yyyy-mm-dd hh24:mi:ss'));
insert into test values ('老王',277,to_date('2017-09-11 13:01:01','yyyy-mm-dd hh24:mi:ss'));
commit;
执行:
with t as
(select name,value,row_number() over (partition by name order by time) rn from test)
select t1.name,t1.value,t2.value,t2.value-t1.value from t t1,t t2 where t1.rn(+)=t2.rn-1 and t1.name=t2.name
结果:
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/3ac79f3df8dcd100dec89981798b4710b9122f3d?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
注意:如果答案被推荐没法追答,请私信联系。
追问谢谢老哥,我意思是张三的下一个整点减去去上一个整点,返回出他的value,十三点减去十二点的值,小时级别的value变化值(同李四老王也要做相同操作)。求解答,辛苦老哥