使用LAG来识别是否为上一个期间间隔，然后仅选择间隔〉1的期间

create table  contracts (employee_id,period,contract) as 
(
  SELECT 111, 202204,'1NA' FROM DUAL UNION ALL
  SELECT 111, 202205,'1NA' FROM DUAL UNION ALL
  SELECT 111, 202206,'1NA' FROM DUAL UNION ALL
  SELECT 111, 202209,'1NA' FROM DUAL UNION ALL
  SELECT 112, 202207,'1NA' FROM DUAL UNION ALL
  SELECT 112, 202208,'1NA' FROM DUAL 
);

Table CONTRACTS created.

with contracts_w_lags (
   employee_id
   ,period
   ,last_period
   ,contract
) as ( select employee_id
             ,period
             ,lag(period)
               over(partition by employee_id
                    order by period)
             ,contract
  from contracts
)
select employee_id
      ,period 
      ,contract
  from contracts_w_lags
 where period - nvl( last_period ,period ) > 1;

EMPLOYEE_ID     PERIOD CON
----------- ---------- ---
        111     202209 1NA

请注意，您的样本数据只有同一年内的时段。如果时段跨年，则此示例将失败。
要解决这个问题，请创建一个伪“periods”表，用行号标识连续的行：

create table  contracts (employee_id,period,contract) as 
(
  SELECT 111, 202111,'1NA' FROM DUAL UNION ALL
  SELECT 111, 202112,'1NA' FROM DUAL UNION ALL
  SELECT 111, 202201,'1NA' FROM DUAL UNION ALL
  SELECT 111, 202203,'1NA' FROM DUAL UNION ALL
  SELECT 112, 202207,'1NA' FROM DUAL UNION ALL
  SELECT 112, 202208,'1NA' FROM DUAL 
);

Table CONTRACTS created.

with month_count ( cnt ) as 
( select months_between(
   to_date( max(period) ,'YYYYMM' )
  ,to_date( min(period) ,'YYYYMM' ))
  from contracts
),contract_start ( dt ) as 
( select to_date( min(period) ,'YYYYMM' )
  from contracts
),contract_periods ( period ,rn ) as 
( select to_char( add_months( c.dt ,level - 1 ) ,'YYYYMM' )
        ,row_number() over( order by add_months( c.dt ,level - 1 ) ) 
    from contract_start c
        ,month_count m 
    connect by level <= m.cnt + 1 
),contracts_w_lags ( employee_id ,period ,contract ,period_rn ,last_period_rn ) as 
( select c.employee_id
        ,c.period
        ,c.contract
        ,p.rn
        ,lag(p.rn) over(partition by c.employee_id order by p.rn )
  from contracts c
       join contract_periods p on c.period = p.period
)
select employee_id
      ,period
      ,contract
  from contracts_w_lags
 where period_rn - nvl( last_period_rn ,period_rn ) > 1;


EMPLOYEE_ID     PERIOD CON
----------- ---------- ---
        111     202209 1NA

答复（在评论之后）在末尾.。

目前尚不清楚此示例数据的预期结果是什么：

WITH
    contracts (EMP_ID, PERIOD, CONTRACT) as 
        (
            SELECT 111, 202204, '1NA' FROM DUAL UNION ALL
            SELECT 111, 202205, '1NA' FROM DUAL UNION ALL
            SELECT 111, 202206, '1NA' FROM DUAL UNION ALL
            SELECT 112, 202207, '1NA' FROM DUAL UNION ALL
            SELECT 112, 202208, '1NA' FROM DUAL UNION ALL
            SELECT 111, 202209, '1NA' FROM DUAL 
        )

两个样本员工都有多个连续期间。其中一个选项是显示具有多个期间的员工的第一个期间和最后一个期间：

SELECT  EMP_ID, Min(PREV_PERIOD) "FIRST_PERIOD", Max(PERIOD) "LAST_PERIOD", CONTRACT
FROM    (Select EMP_ID, PERIOD, CONTRACT, 
              LAG(PERIOD, 1, 0) OVER(Partition By EMP_ID Order By PERIOD) "PREV_PERIOD"
       From contracts)
WHERE   PREV_PERIOD != 0
GROUP BY  EMP_ID, CONTRACT
--  
--  R e s u l t :
--      EMP_ID FIRST_PERIOD LAST_PERIOD CONTRACT
--  ---------- ------------ ----------- --------
--         111       202204      202209 1NA      
--         112       202207      202208 1NA

......另一个可能是向所有人展示：

SELECT  EMP_ID, PERIOD "PERIOD", PREV_PERIOD "PREV_PERIOD", CONTRACT
FROM    (Select EMP_ID, PERIOD, CONTRACT, 
              LAG(PERIOD, 1, 0) OVER(Partition By EMP_ID Order By PERIOD) "PREV_PERIOD"
       From contracts)
WHERE   PREV_PERIOD != 0
--  
--  R e s u l t :
--      EMP_ID     PERIOD PREV_PERIOD CONTRACT
--  ---------- ---------- ----------- --------
--         111     202205      202204 1NA      
--         111     202206      202205 1NA      
--         111     202209      202206 1NA      
--         112     202208      202207 1NA

...如果您希望LEAD（）函数也是如此

SELECT  EMP_ID, PERIOD "PERIOD", NEXT_PERIOD "NEXT_PERIOD", CONTRACT
FROM    (Select EMP_ID, PERIOD, CONTRACT, 
              LEAD(PERIOD, 1, 0) OVER(Partition By EMP_ID Order By PERIOD) "NEXT_PERIOD"
       From contracts)
WHERE   NEXT_PERIOD != 0
--  
--  R e s u l t :
--      EMP_ID     PERIOD NEXT_PERIOD CONTRACT
--  ---------- ---------- ----------- --------
--         111     202204      202205 1NA      
--         111     202205      202206 1NA      
--         111     202206      202309 1NA      
--         112     202207      202208 1NA     
--         112     202208      202207 1NA

它几乎是一样的-只是显示下一个周期而不是上一个周期。

注：如根据某合约重新聘用指同一合约，则─
OVER（按EMP_ID、合同...划分）

相反操作（非连续时段）：

SELECT  EMP_ID, PERIOD "PERIOD", NEXT_PERIOD "NEXT_PERIOD", CONTRACT
FROM    (Select EMP_ID, PERIOD, CONTRACT, 
              LEAD(PERIOD, 1, 0) OVER(Partition By EMP_ID Order By PERIOD) "NEXT_PERIOD"
       From contracts)
WHERE   NEXT_PERIOD != 0 And CASE WHEN SubStr(NEXT_PERIOD, 1, 4) = SubStr(PERIOD, 1, 4) 
                                THEN NEXT_PERIOD - PERIOD
                           ELSE NEXT_PERIOD - (PERIOD + 88)  -- handling the year  change
                           END > 1
--  
--  R e s u l t :
--      EMP_ID     PERIOD NEXT_PERIOD CONTRACT
--  ---------- ---------- ----------- --------
--         111     202206      202209 1NA

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

SELECT  emp_id, period, contract
FROM    contracts
MATCH_RECOGNIZE(
  PARTITION BY contract
  ORDER BY period
  MEASURES
    FIRST(emp_id) AS emp_id,
    LAST(period)  AS period
  AFTER MATCH SKIP TO FIRST different_emp
  PATTERN (emp+ different_emp+ emp)
  DEFINE
    emp           AS FIRST(emp_id) =  emp_id,
    different_emp AS FIRST(emp_id) != emp_id
);

其中，对于示例数据：

CREATE TABLE contracts (EMP_ID, PERIOD, CONTRACT) as 
SELECT 111, 202204, '1NA' FROM DUAL UNION ALL
SELECT 111, 202205, '1NA' FROM DUAL UNION ALL
SELECT 111, 202206, '1NA' FROM DUAL UNION ALL
SELECT 112, 202207, '1NA' FROM DUAL UNION ALL
SELECT 112, 202208, '1NA' FROM DUAL UNION ALL
SELECT 111, 202209, '1NA' FROM DUAL;

输出：
| 电磁脉冲标识|周期|合同|
| - ------| - ------| - ------|
| 一百一十一|小行星202| 1NA|
fiddle