如何在oracle中從連續(xù)但斷開(kāi)的小范圍創(chuàng)建單個(gè)/多個(gè)數(shù)字范圍

您可以使用MATCH_RECOGNIZE執(zhí)行row-by-row模式匹配：

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)
)

對(duì)于樣本數(shù)據(jù)：

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;

Outputs:

fiddle