在oracle数据库中联合查询出多组数据,如何计算相邻两列时间的时间差_百...

发布网友发布时间：2024-10-19 17:51

共1个回答

热心网友时间：2024-10-28 01:05

可以考虑用窗口函数，给一个例子仅供参考。

drop table sub purge;
create table sub (id number, v number);
insert into sub values(1, 2);
insert into sub values(2, 4);
insert into sub values(3, 6);
insert into sub values(4, 8);
insert into sub values(5, 10);
insert into sub values(6, 12);
insert into sub values(7, 14);
insert into sub values(8, 16);
insert into sub values(9, 18);
insert into sub values(10, 20);
commit;

上面是建表和插入一些数据。按照ID排序之后，查询当前行的V减上面一行的V，可以用这个语句。

SELECT FIRST_VALUE(V) OVER (ORDER BY ID ROWS BETWEEN 1 PRECEDING AND CURRENT ROW),
       LAST_VALUE(V) OVER (ORDER BY ID ROWS BETWEEN 1 PRECEDING AND CURRENT ROW),
       LAST_VALUE(V) OVER (ORDER BY ID ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - FIRST_VALUE(V) OVER (ORDER BY ID ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
  FROM SUB;