从Oracle 12中改编my answer to your previous question，您可以UNPIVOT日期，然后使用分析函数和MATCH_RECOGNIZE逐行处理结果集，以查找其中rule 1处于活动状态而rule 2处于非活动状态的连续行：

SELECT item_no,
       item_type,
       active_from
       + CASE
         WHEN prev_rule2 > 0
         THEN INTERVAL '1' SECOND
         ELSE INTERVAL '0' SECOND
         END AS active_from,
       active_to
       - CASE
         WHEN next_rule2 > 0
         THEN INTERVAL '1' SECOND
         ELSE INTERVAL '0' SECOND
         END AS active_to
FROM   (
  SELECT item_no,
         item_type,
         rule_id,
         dt,
         COALESCE(
           SUM(CASE rule_id WHEN 'rule1' THEN active END) OVER (
             PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
           ),
           0
         ) AS rule1,
         COALESCE(
           SUM(CASE rule_id WHEN 'rule2' THEN active END) OVER (
             PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
           ),
           0
         ) AS rule2
  FROM   table_name
         UNPIVOT (
           dt FOR active IN ( active_from AS 1, active_to AS -1 )
         )
)
MATCH_RECOGNIZE(
  PARTITION BY item_no, item_type
  ORDER BY dt, rule1 DESC, rule2 DESC
  MEASURES
    FIRST(dt) AS active_from,
    PREV(rule1) AS prev_rule1,
    PREV(rule2) AS prev_rule2,
    NEXT(dt) AS active_to,
    NEXT(rule1) AS next_rule1,
    NEXT(rule2) AS next_rule2
  PATTERN ( active_rules+ )
  DEFINE active_rules AS rule1 > 0 AND rule2 = 0
)

对于您上一个问题中的样本数据：

CREATE TABLE table_name (Item_no, item_type, active_from, active_to, rule_id) AS
SELECT 10001, 'SAR', DATE '2020-01-01', DATE '2023-01-01', 'rule1' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2024-01-01', DATE '9999-12-31', 'rule1' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2020-05-01', DATE '2021-06-01', 'rule2' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2021-01-01', DATE '2021-02-01', 'rule2' FROM DUAL;

输出：
| 项目编号|项目类型|ACTIVE_FROM| ACTIVE_TO|
| - -----|- -----|- -----|- -----|
| 10001| SAR| 2020-01-01 00：00：00| 2020-04-30 23：59：59|
| 10001| SAR| 2019 -06- 21 00：00：01| 2019 -01- 21 00：00：00|
| 10001| SAR| 2024-01-01 00：00：00| 2019 - 09 -12 00：00：00|
并且，对于本题中的样本数据：

CREATE TABLE table_name (Item_no, item_type, active_from, active_to, rule_id) AS
SELECT 10001, 'SAR', DATE '2022-01-01', DATE '2023-05-31', 'rule1' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2023-07-01', DATE '2099-12-31', 'rule1' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2023-01-01', DATE '9999-12-31', 'rule2' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2020-12-01', DATE '2021-12-31', 'rule2' FROM DUAL;

输出：
| 项目编号|项目类型|ACTIVE_FROM| ACTIVE_TO|
| - -----|- -----|- -----|- -----|
| 10001| SAR| 2019 -01- 22 00：00：00| 2022-12-31 23：59：59|
并且，对于样本数据：

CREATE TABLE table_name (Item_no, item_type, active_from, active_to, rule_id) AS
SELECT 10001, 'SAR', DATE '2022-01-01', DATE '9999-12-31', 'rule1' FROM DUAL UNION ALL
SELECT 10001, 'SAR', DATE '2022-01-01', DATE '2023-05-31', 'rule2' FROM DUAL;

输出：
| 项目编号|项目类型|ACTIVE_FROM| ACTIVE_TO|
| - -----|- -----|- -----|- -----|
| 10001| SAR| 2019 -05- 21 00：00：01| 2019 - 09 -12 00：00：00|
fiddle