在Oracle 12中，可以使用MATCH_RECOGNIZE执行逐行模式匹配：

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY employee
  ORDER BY effective_date
  MEASURES
    FIRST(id) AS id,
    FIRST(position_title) AS position_title,
    FIRST(effective_date) AS supervisor_start,
    NEXT(effective_date) AS supervisor_end
  PATTERN (same_supervisor+)
  DEFINE
    same_supervisor AS
           FIRST(position_title) = position_title
       AND supervisor_status = 'Supervisor'
)

其中，对于样本数据：

CREATE TABLE table_name (ID, Employee, Position_Title, Effective_Date, Supervisor_Status) AS
SELECT 1234, 'John Adams',  'President',      DATE '2020-05-01', 'Supervisor' FROM DUAL UNION ALL
SELECT 1234, 'John Adams',  'President',      DATE '2020-04-01', 'Supervisor' FROM DUAL UNION ALL
SELECT 1234, 'John Adams',  'President',      DATE '2020-03-01', 'Supervisor' FROM DUAL UNION ALL
SELECT 1234, 'John Adams',  'President',      DATE '2020-02-01', 'Supervisor' FROM DUAL UNION ALL
SELECT 1234, 'John Adams',  'President',      DATE '2020-01-01', 'Supervisor' FROM DUAL UNION ALL
SELECT 1234, 'John Adams',  'Staff',          DATE '2017-02-01', 'Non-Supervisor' FROM DUAL UNION ALL
SELECT 1234, 'John Adams',  'Staff',          DATE '2016-04-01', 'Non-Supervisor' FROM DUAL UNION ALL
SELECT 1234, 'John Adams',  'Staff',          DATE '2015-05-01', 'Non-Supervisor' FROM DUAL UNION ALL
SELECT 1234, 'John Adams',  'Staff',          DATE '2014-06-01', 'Non-Supervisor' FROM DUAL UNION ALL
SELECT 1234, 'John Adams',  'Vice President', DATE '2013-01-01', 'Supervisor' FROM DUAL UNION ALL
SELECT 1234, 'John Adams',  'Vice President', DATE '2012-01-01', 'Supervisor' FROM DUAL UNION ALL
SELECT 1234, 'John Adams',  'Staff',          DATE '2017-02-01', 'Non-Supervisor' FROM DUAL UNION ALL
SELECT 1234, 'John Adams',  'Staff',          DATE '2016-04-01', 'Non-Supervisor' FROM DUAL UNION ALL
SELECT 1234, 'John Adams',  'Staff',          DATE '2015-05-01', 'Non-Supervisor' FROM DUAL UNION ALL
SELECT 1234, 'John Adams',  'Staff',          DATE '2014-06-01', 'Non-Supervisor' FROM DUAL UNION ALL
SELECT 1234, 'Stacy Jones', 'President',      DATE '2018-02-01', 'Supervisor' FROM DUAL UNION ALL
SELECT 1234, 'Stacy Jones', 'President',      DATE '2016-02-11', 'Supervisor' FROM DUAL UNION ALL
SELECT 1234, 'Stacy Jones', 'President',      DATE '2015-03-09', 'Supervisor' FROM DUAL UNION ALL
SELECT 1234, 'Stacy Jones', 'Staff',          DATE '2014-01-09', 'Non-Supervisor' FROM DUAL

输出：
| 员工|ID|职位_头衔|主管_开始|主管_结束|
| --------------|--------------|--------------|--------------|--------------|
| 约翰·亚当斯|一二三四|副总裁|2012-01-01 00：00：00|2014-06-01 00：00：00|
| 约翰·亚当斯|一二三四|总裁|2020-01-01 00：00：00|联系我们|
| 斯泰西·琼斯|一二三四|总裁|2015-03-09 00：00：00|联系我们|
对于supervisor_end，如果您想要范围的最后日期，而不是下一个范围的下一个日期，则使用LAST(effective_date)代替NEXT(effective_date)。
它将输出：
| 员工|ID|职位_头衔|主管_开始|主管_结束|
| --------------|--------------|--------------|--------------|--------------|
| 约翰·亚当斯|一二三四|副总裁|2012-01-01 00：00：00|2013-01-01 00：00：00|
| 约翰·亚当斯|一二三四|总裁|2020-01-01 00：00：00|2020-05-01 00：00：00|
| 斯泰西·琼斯|一二三四|总裁|2015-03-09 00：00：00|2018-02-01 00：00：00|
fiddle