如何调整CASE WHEN查询逻辑，让同一Col_1多状态时取'bad'

原表 TABLE_1 #

原查询语句 #

SELECT DISTINCT
   Col_1
   , CASE WHEN Col_3 = 100 THEN 'good'
      ELSE 'bad'
   END AS STATUS
FROM TABLE_1

原查询结果 TABLE_2 #

需求 #

当同一Col_1同时存在"good"和"bad"状态时，仅保留"bad"状态，得到如下目标结果：

目标结果 TABLE_3 #

修改后的查询方案 #

方法一：分组判断是否存在非100记录

通过子查询判断每个Col_1下是否存在Col_3不等于100的记录，直接确定最终状态：

SELECT
    Col_1,
    CASE WHEN EXISTS (
        SELECT 1 FROM TABLE_1 t2 
        WHERE t2.Col_1 = t1.Col_1 AND t2.Col_3 != 100
    ) THEN 'bad'
    ELSE 'good'
    END AS STATUS
FROM TABLE_1 t1
GROUP BY Col_1

方法二：利用聚合函数优先级取值

利用字符串的ASCII码特性（'bad'的首字母ASCII码大于'good'），用MAX函数直接取优先级更高的状态：

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

方法三：窗口函数标记后筛选

先通过窗口函数标记每个Col_1是否存在bad状态，再筛选出最终结果：

WITH status_cte AS (
    SELECT 
        Col_1,
        CASE WHEN Col_3 = 100 THEN 'good' ELSE 'bad' END AS STATUS,
        MAX(CASE WHEN Col_3 != 100 THEN 1 ELSE 0 END) OVER (PARTITION BY Col_1) AS has_bad
    FROM TABLE_1
)
SELECT DISTINCT
    Col_1,
    CASE WHEN has_bad = 1 THEN 'bad' ELSE 'good' END AS STATUS
FROM status_cte

内容的提问来源于stack exchange，提问作者NEWBIE