比较 i 和 i+1 元素预言机

Question

我有表格

CREATE table table_x 
(
        id NUMBER,  -- ID
        NUMBER_history NUMBER, --  consecutive number
        start_date date,    -- start_date  of next id=end_date of previous
        end_date date   -- should be >=start_date 
);
 INSERT INTO table_x VALUES  (14 , 1, '13-NOV-92' ,'14-NOV-92' );
 INSERT INTO table_x VALUES  (15 , 2, '14-NOV-92' ,'16-NOV-92' );
 INSERT INTO table_x VALUES  (19 , 3, '16-NOV-92' ,'18-NOV-92' );
 INSERT INTO table_x VALUES  (11 , 4, '18-NOV-92' ,'14-NOV-93' );
 INSERT INTO table_x VALUES  (17 , 5, '15-NOV-93' ,'16-NOV-93' );
 INSERT INTO table_x VALUES  (151 , 6, '16-NOV-93' ,'17-NOV-93' );
 INSERT INTO table_x VALUES  (139 , 7, '17-NOV-93' ,'18-NOV-93' );
 INSERT INTO table_x VALUES  (121 , 8, '19-NOV-93' ,'20-DEC-93' );
 INSERT INTO table_x VALUES  (822 , 9, '20-DEC-93' ,'20-DEC-93' );

我想写查询，在哪里可以找到下一行> end_date上一行的start_date。 它们必须是相等的。

我尝试使用NUMBER_history作为计数器来做类似的事情。C 方式，我按变量 i 组织循环并比较 i 和 i+1（NUMBER_history 和 NUMBER_history+1）

select * INTO row_tblx from table_x where NUMBER_history=NUMBER_history and end_date<(select start_date from table_x where NUMBER_history=NUMBER_history+1);

但我必须通过从 1 到最后一个值n_counter来组织循环NUMBER_history并将数据提取到多行中。我该怎么做？
我尝试

set serveroutput on
DECLARE
CURSOR cur IS
      SELECT * FROM table_x;
TYPE row_tblx_type
IS
TABLE OF cur%ROWTYPE;
row_tblx row_tblx_type;
  rowx  cur%ROWTYPE;
  nh_count NUMBER;
BEGIN
FOR NUMBER_history IN cur LOOP
select * INTO row_tblx from table_x where NUMBER_history=NUMBER_history and end_date<(select start_date from table_x where NUMBER_history=NUMBER_history+1);
DBMS_OUTPUT.PUT_LINE (row_tblx(NUMBER_history).id);
END LOOP;
END;
/

如何使用 for 或其他循环、多条记录或记录表、光标、表行作为计数器 （NUMBER_history） 来做到这一点？如何在没有光标的情况下做到这一点？

Answer 1

你不需要PL/SQL或循环：

select *
from (
   select id, number_history, start_date, end_date, 
          lead(start_date) over (order by number_history) as next_start
   from table_x
) t
where next_start > end_date;

Answer 2

例如，这是一个不需要PL/SQL或LEAD/LAG函数的解决方案

select a.*, b.*
from table_x a
join table_x b
  on a.number_history+1 = b.number_history
where b.start_date > a.end_date