像这样的，也许？
样本数据：

SQL> with table_1 (col1, col2, col3) as
  2    (select 'A', 'Apple',  100 from dual union all
  3     select 'A', 'Apple',  100 from dual union all
  4     select 'A', 'Apple',   56 from dual union all
  5     select 'A', 'Apple',   44 from dual union all
  6     select 'B', 'Banana' ,100 from dual union all
  7     select 'C', 'Cucumber',93 from dual union all
  8     select 'C', 'Cucumber', 7 from dual
  9    )

查询方式：

10  select col1,
 11    case when max(col3) = 100 and min(col3) < 100 then 'bad'
 12         when max(col3) = 100                     then 'good'
 13         else 'bad'
 14    end status
 15  from table_1
 16  group by col1;

COL1  STATUS
----- ----------
A     bad
B     good
C     bad

SQL>

或者更简单

10  select col1,
 11    min(case when col3 = 100 then 'good'
 12             else 'bad'
 13        end
 14       ) status
 15  from table_1
 16  group by col1;

COL1  STATUS
----- ----------
A     bad
B     good
C     bad

SQL>

您可以通过使用min（）来实现这一点

SELECT DISTINCT Col_1
      ,CASE WHEN min(Col_3) = 100 THEN 'good' ELSE 'bad' END AS STATUS
  FROM TABLE_1
 group by col_1
 order by col_1;

它将采用col_3的最小值，并且当有< 100的记录时将返回“bad”

可以使用GROUP BY和聚合函数

SELECT 
   Col_1
   , MIN(CASE WHEN Col_3 = 100 THEN 'good'
      ELSE 'bad'
   END) AS STATUS
FROM TABLE_1
GROUP BY Col_1

| COL_1|地位|
| --|--|
| 一|坏|
| B|好|
| C|坏|
fiddle