在Oracle 12中，您可以UNPIVOT日期，然后使用分析函数和MATCH_RECOGNIZE逐行处理结果集，以查找两个规则都处于活动状态的连续行：

SELECT *
FROM   (
  SELECT item_no,
         item_type,
         rule_id,
         dt,
         SUM(CASE rule_id WHEN 'rule1' THEN active END) OVER (
           PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
         ) AS rule1,
         SUM(CASE rule_id WHEN 'rule2' THEN active END) OVER (
           PARTITION BY item_no, item_type ORDER BY dt, ACTIVE DESC
         ) 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,
    NEXT(dt) AS active_to
  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-05-01 00：00：00| 2019 -06-01 00：00：00|
并用于：

CREATE TABLE table_name (Item_no, item_type, active_from, active_to, rule_id) AS
SELECT 10001, 'SPR', DATE '2023-01-01', DATE '2023-01-31', 'rule1' FROM DUAL UNION ALL
SELECT 10001, 'SPR', DATE '2023-01-31', DATE '2023-02-27', 'rule2' FROM DUAL;

输出为：
| 项目编号|项目类型|ACTIVE_FROM| ACTIVE_TO|
| - -----|- -----|- -----|- -----|
| 10001| SPR| 2019 -01- 21 00：00：00| 2019 -01- 21 00：00：00|
fiddle

您要查找的是同一项的重叠日期范围，但规则不同。
两个日期范围A和B何时重叠？答案：当start（B）<= end（A）AND end（B）>= start（A）时。有了这些知识，我们就可以加入。重叠范围仅为较大的开始日期到较小的结束日期。

select
  a.item_no,
  a.item_type,
  greatest(a.active_from, b.active_from) as active_from,
  least(a.active_to, b.active_to) as active_to
from mytable a
join mytable b on b.item_no      =   a.item_no
              and b.item_type    =   a.item_type
              and b.rule         <>  a.rule
              and b.active_from  <=  a.active_to
              and b.active_to    >=  a.active_from;