您可以使用MATCH_RECOGNIZE执行逐行模式匹配：

SELECT main_product,
       sub_product,
       start_serial,
       end_serial
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY sub_product
  ORDER BY start_serial
  MEASURES
    FIRST(main_product) AS main_product,
    FIRST(start_serial) AS start_serial,
    MAX(end_serial) AS end_serial
  PATTERN (overlaps* last_row)
  DEFINE
    overlaps AS MAX(end_serial) + 1 >= NEXT(start_serial)
)

其中，对于样本数据：

CREATE TABLE table_name (Main_Product, Sub_Product, Start_Serial, End_Serial) AS
SELECT 'Main01', 'sub01', 166255, 166258 FROM DUAL UNION ALL
SELECT 'Main01', 'sub02', 666255, 666258 FROM DUAL UNION ALL
SELECT 'Main02', 'sub01', 166259, 166262 FROM DUAL UNION ALL
SELECT 'Main02', 'sub02', 666259, 666262 FROM DUAL UNION ALL
SELECT 'Main03', 'sub01', 166267, 166270 FROM DUAL UNION ALL
SELECT 'Main03', 'sub02', 666267, 666270 FROM DUAL;

输出：
| 主营产品|子产品|开始_序列|结束_序列|
| - -----|- -----|- -----|- -----|
| 主01|联系我们|一六六二五五|166262|
| Main03|联系我们|一六六二六七|166270|
| 主01|亚02|六六六二五五|666262|
| Main03|亚02|六六六二六七|666270|
fiddle