可以使用HAVIN COUNT DISTINCT
我已经添加了一个重复的行，以显示它的作品

CREATE TABLE MM_LOT_INV_ST_LG 
    ("LOT_ID" int, "MATERIAL_ID" varchar2(18))
;

x

INSERT ALL 
    INTO MM_LOT_INV_ST_LG  ("LOT_ID", "MATERIAL_ID")
         VALUES (1000585855, 'Chips Batch 1')
    INTO MM_LOT_INV_ST_LG  ("LOT_ID", "MATERIAL_ID")
         VALUES (1000585856, 'Chips Batch 2')
    INTO MM_LOT_INV_ST_LG  ("LOT_ID", "MATERIAL_ID")
         VALUES (1000586452, 'SMD Tape Batch 1')
    INTO MM_LOT_INV_ST_LG  ("LOT_ID", "MATERIAL_ID")
         VALUES (1000586453, 'SMD Tape Batch 2')
    INTO MM_LOT_INV_ST_LG  ("LOT_ID", "MATERIAL_ID")
         VALUES (1000456987, 'Cover Tape Batch 1')
      INTO MM_LOT_INV_ST_LG  ("LOT_ID", "MATERIAL_ID")
         VALUES (1000456987, 'Cover Tape Batch 5')
SELECT * FROM dual
;

6 rows affected

SELECT "LOT_ID" FROM MM_LOT_INV_ST_LG GROUP BY "LOT_ID" HAVING  COUNT(DISTINCT "MATERIAL_ID") > 1

的数据
| LOT_ID |
| ------------ |
| 1000456987 |
fiddle
如果他们都有共同的智力，你可以简单地排除他们

CREATE TABLE MM_LOT_INV_ST_LG 
    ("LOT_ID" int, "MATERIAL_ID" varchar2(40))
;

INSERT ALL 
    INTO MM_LOT_INV_ST_LG  ("LOT_ID", "MATERIAL_ID")
         VALUES (1000585855, 'Chips Batch 1')
    INTO MM_LOT_INV_ST_LG  ("LOT_ID", "MATERIAL_ID")
         VALUES (1000585856, 'Chips Batch 2')
    INTO MM_LOT_INV_ST_LG  ("LOT_ID", "MATERIAL_ID")
         VALUES (1000586452, 'SMD Tape Batch 1')
    INTO MM_LOT_INV_ST_LG  ("LOT_ID", "MATERIAL_ID")
         VALUES (1000586453, 'SMD Tape Batch 2')
    INTO MM_LOT_INV_ST_LG  ("LOT_ID", "MATERIAL_ID")
         VALUES (1000456987, 'Cover Tape Batch 1')
      INTO MM_LOT_INV_ST_LG  ("LOT_ID", "MATERIAL_ID")
         VALUES (1000456987, 'INT Cover Tape Batch 5')
SELECT * FROM dual
;

6 rows affected

SELECT "LOT_ID"  a FROM MM_LOT_INV_ST_LG 
  WHERE SUBSTR("MATERIAL_ID",1,3) <> 'INT' 
  GROUP BY "LOT_ID"
  HAVING  COUNT(DISTINCT "MATERIAL_ID") > 1

fiddle的